我想创建一个查询,它将能够在我的"标准"中使用一个短语的单词(像谷歌一样)
的例子:我的短语:'Foo Bar Test'
我的数据库表:
| |列
"你好棒"
测试棒的
"嗨Foo"
"也没有"
再次"Foo"
我正在等待的结果:
| |列
"你好棒"
测试棒的
"嗨Foo"
再次"Foo"
任何拆分/解析函数都可以提供帮助
Declare @YourTable table (YourFieldName varchar(100))
Insert Into @YourTable values
('Hi Bar'),
('Test Ba'),
('Hi Foo'),
('Nothing here'),
('Foo again')
Declare @SearchString varchar(max) = 'Foo Bar Test'
Select A.*
From @YourTable A
Join [dbo].[udf-Str-Parse](@SearchString,' ') B
on Charindex(RetVal,YourFieldName)>0
返回YourFieldName
Hi Foo
Foo again
Hi Bar
Test Ba
如果需要的话UDF
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
您可以在Sql server中使用FULL Text Index
来完成此操作。也可以使用拆分字符串函数来实现
Declare @str varchar(500) = 'Foo Bar Test'
SELECT distinct tc.*
FROM (VALUES ('Hi Bar' ),
('Test Bar' ),
('Hi Foo' ),
('Nothing here' ),
('Foo again' ) ) tc (columnss)
JOIN [dbo].[Delimitedsplit8k](@str, ' ') f
ON columnss LIKE '%' + f.Item + '%'
要创建拆分字符串函数,请查看此链接http://www.sqlservercentral.com/articles/Tally+Table/72993/