Split Phrase in words with single quotes & comma in SQL Server using TSQL

Question

I have a search query where I use the Like operator to search keyword, now I need to provide user with an option to search as exact phase or search all words based on search keywords

Search Keyword = mission to mars

Search Query

 Select * from Table` WHERE  Title LIKE '%mission to mars%' OR Details LIKE '%mission to mars%'

Now I need to convert this query so that it search for all words from search keywords mission to mars

So my query should be like

Select * from Table` WHERE  Title IN ('mission', 'to', 'mars') OR Details IN ('mission, 'to', 'mars')

Can I using any SQL function break the search keyword mission to mars and convert it into string with each word wrapped in single quote & separated by comma example 'mission', 'to', 'mars'

STRING_SPLIT could have been the solution but it works in SQL SERVER 2016 & above while I am using SQL 2014.

I can split the keyword from C# it self and pass it as 'mission', 'to', 'mars' to stored procedure also but i am interested if i can split it in T-SQL itself

I don't think you want `IN` for this. I think you want `Title LIKE '%mission%' OR Titile LIKE '%mars%' OR...` — Zohar Peled, Oct 28 '19 at 07:12
Would this help: https://stackoverflow.com/questions/10914576/t-sql-split-string — VBoka, Oct 28 '19 at 07:14
You mentioned c# in your question - so my advice would be to send the stored procedure a table valued parameter instead of a string to be splitted in T-SQL (remembering that string manipulation is not T-SQL strong suit). Should you choose to split the string in SQL, you should read Aaron Bertrand's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Zohar Peled, Oct 28 '19 at 07:14
@ZoharPeled, I need IN as in will look for all there words if past it a string similar to 'mission', 'to', 'mars', regarding you second comment i can using C# but i am interested to know if it can be done easily in T-SQL also — Learning, Oct 28 '19 at 07:16
`X IN('1','2','3')` is shorthand for `X = '1' OR X = '2' OR X = '3'`. Do you expect title or details to contain only a single word? — Zohar Peled, Oct 28 '19 at 07:20
@ZoharPeled, I need to search in Title, Details and other fields as well such as brief Description which i have not included in the question for sake of simplicity, i have to usually search keywords in 3-r fields of table — Learning, Oct 28 '19 at 07:25
Have a read up on Full-Text Search in SQL Server: https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15 — gvee, Oct 28 '19 at 07:47
Can you split a string in SQL? Yes you can. Would you want to do it - probably not. Simple fixed delimiter string splitting SQL functions can be easily found online. I believe your case would not be simple though, as you will need to take into account punctuation, noise words etc. Full-Text Search (see @gvee comment) in SQL or C# solutions would be more appropriate. — Alex, Oct 28 '19 at 07:52
@Full-Text is not installed on the server :( so i think easy option would to to pass it from C# itself as variable to Store Procedure — Learning, Oct 28 '19 at 08:01
@ZoharPeled, I understand what you meant by your first comments — Learning, Oct 28 '19 at 08:41

forpas · Answer 1 · 2019-10-28T10:59:18.970

You can simulate STRING_SPLIT()'s functionality with a recursive CTE that will return all the words of the phrase:

declare @s varchar(100) = 'mission to mars';
with 
  cte as (
    select 1 n1, charindex(' ', @s) n2
    union all
    select n2 + 1, charindex(' ', @s, n2 + 1)
    from cte
    where n2 > 0
  ),
  words as (
    select substring(
      @s, 
      n1, 
      case when n2 > 0 then n2 - n1 else len(@s) end
    ) word
    from cte
  )
select * from words;

See the demo.
Results:

> | word    |
> | :------ |
> | mission |
> | to      |
> | mars    |

So that you can use it like this:

select * from tablename WHERE Title IN (select * from words)

Kemal AL GAZZAH · Answer 2 · 2019-10-28T08:58:34.620

--Create a function 'fn_split_string ' which splits a string

create function dbo.fn_split_string(@str nvarchar(1000),@sep nvarchar(1))
returns @tab_split  table (id int identity(1,1),str nvarchar(1000) )  
as  

begin  
declare @str1 as nvarchar(1000)  
declare @istr as nvarchar(1000)  
declare @i as integer  
set @str1=@str  
set @i=2  
while @i>0 
begin  
set @i=charindex(@sep,@str1,1)  
if @i>0  
begin  
set @istr=substring(@str1,1,@i-1)  
insert into @tab_split(str) values(@istr)  
set @str1=substring(@str1,@i+1,len(@str1))  
end  
end  
insert into @tab_split(str) values(@str1)  
return   
end

--You can run the function as follows

select * from dbo.fn_split_string ('mission to mars',' ')

--The following script

Select * from Table` WHERE  Title IN ('mission', 'to', 'mars') OR Details IN ('mission, 'to', 'mars')

--becomes

select * from Table t1 inner join dbo.fn_split_string ('mission to mars',' ') t2
on t1.title=t2.str or t1.details=t2.str

--hope this helps

--**

Another Solution which avoids using loops, as "Zohar Peled" adviced

**

create table tab_numbers (Number int);

with cte as (select 1 n union all select 1+n from cte where n+1 <=100)
insert into tab_numbers select * from cte
option(maxrecursion 0)

go




create FUNCTION fn_split_string_2
(
   @str       NVARCHAR(MAX),
   @sep  NVARCHAR(255)
)
RETURNS @tab_split table (str  nvarchar(max))

AS
  begin
  if @sep=' ' 
  begin
    set @sep=',' 
    set @str=replace(@str,' ',',')
  end


   insert into @tab_split

       SELECT Item = SUBSTRING(@str, Number, 
         CHARINDEX(@sep, @str + @sep, Number) - Number)
       FROM tab_numbers
       WHERE Number <= CONVERT(INT, LEN(@str))
         AND SUBSTRING(@sep + @str, Number, LEN(@sep)) = @sep
   ;
   return
   end
GO

I edited my answer and corrected the last script, it should be "select * from Table t1 inner join dbo.fn_split_string ('mission to mars',' ') t2 on t1.title=t2.str or t1.details=t2.str" — Kemal AL GAZZAH, Oct 28 '19 at 08:17
Please don't split strings using a loop. That's the worst possible way to do it. Read the article I've linked to in the comments to the question to find better ways to do that. — Zohar Peled, Oct 28 '19 at 08:21
thank you for the advice, I posted another function which does not use loops — Kemal AL GAZZAH, Oct 28 '19 at 08:59
Using multi-statement table valued functions is also a terrible approach to this. Neither of the function in this answer should be used as the performance is going to be horrible for even reasonably long strings. — Sean Lange, Oct 28 '19 at 14:42

Split Phrase in words with single quotes & comma in SQL Server using TSQL

2 Answers2