Word Search Using the SQL Server Query Phrase

Question

I would like to create a query that could use phrase words in my “criteria” (for example, google)

Example: My phrase: "Foo Bar Test"

My database table:

| Column |

'Hello Bar'

'Test Bar'

'Hello Foo'

"Nothing here"

'Foo again'

The result I'm waiting for:

| Column |

'Hello Bar'

'Test Bar'

'Hello Foo'

'Foo Again'

Answer 1

Any split / parse function can help

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

Returns

YourFieldName
Hi Foo
Foo again
Hi Bar
Test Ba

UDF if necessary

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',' ')

Answer 2

, FULL Text Index Sql. split string

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 + '%' 

split string, http://www.sqlservercentral.com/articles/Tally+Table/72993/