How to match any value of search string from a column containing multiple values separated by space in table in sql?

I have a column in table which has multiple values separated by space. i want to return those rows which has any of the matching values from search string.

Eg:

search string= 'mumbai pune'

This need to return rows matching word 'mumbai' or 'pune' or matching both

Declare @str nvarchar(500)
SET @str='mumbai pune'

create table #tmp
(
ID int identity(1,1),
citycsv nvarchar(500)
)


insert into #tmp(citycsv)Values
('mumbai pune'),
('mumbai'),
('nagpur')

select *from #tmp t

select *from #tmp t
where t.citycsv like '%'+@str+'%'

drop table #tmp

Required Out put:

ID CityCSV
1  mumbai pune
2  mumbai

Solution

You can use a splitter function to split your search string out as a table contain the desired search keys. Then you can join your main table with the table containing the search key using the LIKE statement.

For completeness I have included an example of a string splitter function, however there are plenty of example here on SO.

Example string splitter function:

CREATE FUNCTION [dbo].[SplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

The following query demonstrates how the string splitter function can be combined with regular expressions to get the desired result:

SELECT      DISTINCT 
            C.ID
            ,C.citycsv
FROM        #tmp C
INNER JOIN  (
                SELECT  splitdata + '[ ]%' AS MatchFirstWord            -- Search pattern to match the first word in the string with the target search word.
                        ,'%[ ]' + splitdata AS MatchLastWord            -- Search pattern to match the last word in the string with the target search word.
                        ,'%[ ]' + splitdata + '[ ]%' AS MatchMiddle     -- Search pattern to match any words in the middle of the string with the target search word.
                        ,splitdata AS MatchExact                        -- Search pattern for exact match.
                FROM    dbo.SplitString(@str, ' ')
            ) M ON (
                        (C.citycsv LIKE M.MatchFirstWord) OR 
                        (C.citycsv LIKE M.MatchLastWord) OR
                        (C.citycsv LIKE M.MatchMiddle) OR
                        (C.citycsv LIKE M.MatchExact)
                    )
ORDER BY    C.ID