How to make a search query where contain each words of string in SQL Server?

Question

I want to make a search sql query to find a name where contain the string that users input. It is something like the query below, but I don't know how to make the 'where' part. I have been looking in google but I still can't find the right one.

 DECLARE @string varchar(20) 
 SELECT @string = 'test complete name'
 SELECT complete_name from users 
    where complete_name like '%test%'
    or complete_name like '%complete%'
    or complete_name like '%name%'
    or complete_name like '%test complete%'
    or complete_name like '%test name%'
    or complete_name like '%complete name%'
    or complete_name like '%test complete name%'

Answer 1

Create a function like below that splits the given string and returns the individual words from given input

Create function fn_stringSplit(@StringSplit varchar(max))
returns @table table(SplitValue varchar(10) not null)
as
begin
    Declare @StartVal int
    Declare @endVal int
    set @StringSplit = @StringSplit + ' '
    set @StartVal = 1
    set @endVal = 1
    while @endVal >= 0
        begin
            set  @endVal = charindex(' ',@StringSplit,@StartVal);
            insert into @table select SUBSTRING(@StringSplit,@StartVal,@endVal-1)
            set @StringSplit= SUBSTRING(@StringSplit,@endVal+1, LEN(@StringSplit))
            if @StringSplit = '' set @endVal= -1
        end
    return
end

Now call our function in the main query by passing the input

DECLARE @string varchar(20) 
SELECT @string = 'Marketing tool designer'
SELECT JobTitle from Employee e
   where JobTitle in (select e.JobTitle from fn_stringSplit(@string) ss
                            where e.JobTitle like '%'+SplitValue+'%')  --Here Splitvalue is the column name in the table that is returned by fn_stringSplitfunction

in SQL SERVER 2016 we have a function String_Split we can write query as

DECLARE @string varchar(20) 
SELECT @string = 'Marketing tool designer'
SELECT JobTitle from Employee e
   where JobTitle in (select e.JobTitle from String_Split(@string) ss
                            where e.JobTitle like '%'+Value+'%') --Here **value** is the column name in the table that is returned by **String_Split**

Answer 2

you can create function to split you string into temp table of word. then then use in query.

To create function you can follow the link T-SQL split string

DECLARE @string varchar(20) 
SELECT @string = 'test complete name'
SELECT complete_name from users 
   where complete_name in (dbo.splitstring(@string))

Take care of two things

1) Reference link uses comma separator, you will need space

2) in query will do exact search. You will need to write some more logic for like query. e.g. Pass your column value "complete_name" in created function. use like comparison inside function and return 1 Or 0. So, where will become

DECLARE @string varchar(20) SELECT @string = 'test complete name' SELECT complete_name from users where 1 = dbo.test_complete_name(@string,complete_name)

Answer 3

Below is a method, which won't require any hard coding or creation of any additional function. I have tried describing it in different steps for easier understanding.

 DECLARE @string varchar(20) ,@stringxml xml,@rowvalue varchar(20)
 SELECT @string = 'test complete name'
 --Convert the string to an xml 
  Select @stringxml=CAST(('<a>'+replace(@string,' ','</a><a>')+'</a>' )as xml)

--Split the xml to rows based on each word.i.e. each word to one row using nodes() 
 select Row_values.value('.' ,'varchar(max)')
 AS StringValues
 FROM 
 @stringxml.nodes('/a') As StrXml(Row_values)

The above query returns a table with rows having each word.This table can joined with your table to do the required search.

The below query is what you need as a final answer

DECLARE @string varchar(20) ,@stringxml xml,@rowvalue varchar(20)
 SELECT @string = 'test complete name'

--convert string to xml
Select @stringxml=CAST(('<a>'+replace(@string,' ','</a><a>')+'</a>' )as xml)

--Inner join your table to the value from nodes
 Select DISTINCT Urtbl.[ColName] from [YourTableName] Urtbl inner join(
select Row_values.value('.' ,'varchar(max)')
AS StringValues
FROM 
@stringxml.nodes('/a') As StrXml(Row_values)) newtbl
on Urtbl.[ColName] like '%'+newtbl.StringValues+'%'