There is no a simple operator in SQL Server that will do the match. You need to use a user function for this.
First, I suggest reading this
wonderful question[
^] on stack overflow. It will teach you how to write a user function that returns a table. Then, you can grab any of the proposed solution for the Split function and write your own function called MatchPhrase.
Suppose, you've taken this solution for your split function (it is not my function, I just pasted it from the link above):
create function dbo.SplitString
(
@str varchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
Now, all you need is to write this simple function:
CREATE FUNCTION dbo.MatchPhrase
(
@Phrase1 varchar(500),
@Phrase2 varchar(500)
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT
SET @Result = 0
IF NOT EXISTS((SELECT s FROM dbo.SplitString(@Phrase1, ' ')
EXCEPT
SELECT s FROM dbo.SplitString(@Phrase2, ' '))
UNION ALL
(SELECT s FROM dbo.SplitString(@Phrase2, ' ')
EXCEPT
SELECT s FROM dbo.SplitString(@Phrase1, ' '))
)
SET @Result = 1
RETURN @Result
END
GO
And that's it. All you need to do is write this SQL statement:
SELECT * FROM UserTable WHERE dbo.MatchPhrase(subject, 'SQL Server 2005') = 1
Use
this SQL Fiddle[
^] as a playground