CODEWITHSUNDEEP
csvsql-server-2012sql-like
SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

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

Upvotes: 1

Views: 113

Answers (2)

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

Another approach , by using ReplaceFunction

Its syntax as following:

REPLACE ( string_expression , string_pattern , string_replacement )

so we could reach the target via replacing the every space that separated the values with the next pattern 

 '%'' OR t.citycsv like ''%'

An example:

    Declare @str nvarchar(500),
            @Where nvarchar (1000),
            @Query nvarchar (4000)
    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

    Set @Where = 'where t.citycsv like ' +  '''%'+ replace (RTRIM(LTRIM(@str)), ' ', '%'' OR t.citycsv like ''%') +'%'''

    Set @Query = 'select * from #tmp t ' + @Where

    execute sp_executesql @Query 

    drop table #tmp

The Result:

enter image description here

Upvotes: 1

Edmond Quinton
Edmond Quinton

Reputation: 1739

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

Upvotes: 1

Related Questions