CODEWITHSUNDEEP
sql-servercsv
pramodc84
pramodc84

Reputation: 1614

Return rows which matches CSV column to CSV search string?

I have a database table, that has column which stores comma separated string values. Say for example

Row   CSVColumn
1.    value1, value2
2.    value1, value3
3.    value2, value4

My search parameter is again comma separated say value2, value4

Now I want to return all those rows which matches search parameter. In this example should return Row 1 and Row 3.

Can somebody help in writing MS SQL query for this?

Upvotes: 0

Views: 1021

Answers (1)

Darka
Darka

Reputation: 2768

How About this example. I am splitting string into rows for Search string and for CSVColumn.

After that I just INNER JOIN them.

I am using xml for splitting but you also can create function for this.

DECLARE @CsvTable TABLE (Rowid INT, CsvColumn VARCHAR(MAX))

INSERT INTO @CsvTable SELECT '1', 'value1'
INSERT INTO @CsvTable SELECT '2', 'value1, value3'
INSERT INTO @CsvTable SELECT '3', 'value2, value4'

DECLARE @SearchText VARCHAR(20) = 'value2, value3' 

;WITH SearchTable AS (
    SELECT LTRIM(O.splitdata) AS Searchdata
    FROM (
        SELECT CAST('<X>'+replace(@SearchText,',','</X><X>')+'</X>' AS XML) AS XmlData
        ) AS xT
     CROSS APPLY
     ( 
         SELECT xdata.D.value('.', 'VARCHAR(50)') AS splitdata 
         FROM xT.XmlData.nodes('X') as xdata(D)
     ) O
), SplitTable AS (

    SELECT xT.Rowid,
     LTRIM(O.splitdata) AS splitdata
    FROM (
        SELECT T.Rowid, CAST('<X>'+replace(T.CsvColumn,',','</X><X>')+'</X>' AS XML) AS XmlData
        FROM @CsvTable AS T
        ) AS xT
     CROSS APPLY
     ( 
         SELECT xdata.D.value('.', 'VARCHAR(50)') AS splitdata 
         FROM xT.XmlData.nodes('X') as xdata(D)
     ) O

)
SELECT DISTINCT CT.*
FROM @CsvTable AS CT
INNER JOIN SplitTable AS SplitT 
    ON SplitT.RowID = CT.RowID
INNER JOIN SearchTable AS SrchT
    ON SrchT.Searchdata = SplitT.splitdata

Upvotes: 2

Related Questions