You must Sign In to post a response.
  • Category: SQL Server

    How to search comma separated values with comma separated?

    Hi all,

    How to search the comma separated values with comma separated values in SQL. For example:We have four columns in a table with four rows

    in row1 we have 1,2,3
    in row2 we have 2,3,4
    in row3 5,4,3 and
    in row4 we have 2,4,5

    If we search 2, 3 then row1 and row2 should display.

    Regards
    Vivek Sharma
  • #730874
    Hi Vivek,


    Try like this...

    SELECT COLUMN1,COLUMN2,COLUMN3 FROM
    (SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
    +','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
    (SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
    UNION ALL
    SELECT 2,3,4
    UNION ALL
    SELECT 5,4,3
    UNION ALL
    SELECT 2,4,5)A)B
    WHERE COLUMN4 LIKE '%2,3%'


    [OR]

    SELECT * FROM
    (SELECT '1,2,3' AS Column1
    UNION ALL
    SELECT '2,3,4'
    UNION ALL
    SELECT '5,4,3'
    UNION ALL
    SELECT '2,4,5')A
    where Column1 like '%2,3%'



    Regards,
    Sri

  • #730875
    Thanks for your reply Sri, how to implement the same if we want to search 2, 4 w.r.t. above example?


    Regards
    Vivek Sharma

  • #730877
    Hi vivek,

    I Update my previous post try that, if it is not working means please let me know and confirm me if you give 2,4 means you want to show row 2 and row 4?


    Regards,
    Sri

  • #730880
    Sri, I am not getting desire output, Yes 1,3 means we want to show row 1

  • #730881
    Hi Vivek,

    Try this method...

    SELECT COLUMN1,COLUMN2,COLUMN3 FROM
    (SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
    +','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
    (SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
    UNION ALL
    SELECT 2,3,4
    UNION ALL
    SELECT 5,4,3
    UNION ALL
    SELECT 2,4,5)A)B
    WHERE COLUMN4 LIKE replace('%2,4%',',','%')

    [OR]

    DECLARE @A VARCHAR(10)
    SET @A='2,4' ----OR SET @A=replace('2,4',',','%')

    SELECT COLUMN1,COLUMN2,COLUMN3 FROM
    (SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
    +','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
    (SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
    UNION ALL
    SELECT 2,3,4
    UNION ALL
    SELECT 5,4,3
    UNION ALL
    SELECT 2,4,5)A)B
    WHERE COLUMN4 LIKE replace('%'+@A+'%',',','%')


    Regards,
    Sri

  • #730882
    Hi Vivek...

    Please try my above post definitely it will work...


    Regards,
    Sri

  • #730883
    Thanks for quick response, this is working little better, but the query fails in case the parameters changes like 12 , 13 , 14 etc. as you can understand in case like keyword.

    Regards
    Vivek Sharma

  • #730885
    Thank you vivek can you give some more example for that?

    Regards,
    Sri

  • #730886
    Sri, you can check the below example: I think it will not work with "like".


    DECLARE @A VARCHAR(10)
    SET @A='2,4' ----OR SET @A=replace('2,4',',','%')

    SELECT COLUMN1,COLUMN2,COLUMN3 FROM
    (SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
    +','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
    (SELECT 11 AS COLUMN1,12 AS COLUMN2, 13 AS COLUMN3
    UNION ALL
    SELECT 22,23,24
    UNION ALL
    SELECT 5,4,3
    UNION ALL
    SELECT 2,4,5)A)B
    WHERE COLUMN4 LIKE replace('%'+@A+'%',',','%')


    Regards
    Vivek Sharma

  • #730887
    Hi vivek,

    Try this proc...

    Note : If you use below sp your input will check all the three columns....

    for eg: if your input is '1,2'

    it shows 1,2,3
    3,1,2
    1,3,2

    ALTER PROC SAMPLESP(@A VARCHAR(100))
    AS
    BEGIN

    DECLARE @B VARCHAR(100)
    DECLARE @X VARCHAR(100),@Y VARCHAR(100),@Z VARCHAR(100),@LOOP INT

    SET @A=@A+','

    SET @LOOP=1
    WHILE (CHARINDEX(',',@A,0)<>0)
    BEGIN
    SET @B=LEFT(@A,(CHARINDEX(',',@A,0)-1))
    SET @A=SUBSTRING(@A,(CHARINDEX(',',@A,0)+1),LEN(@A)-LEN(@B))
    IF @LOOP=1
    SET @X=@B

    ELSE IF @LOOP=2
    SET @Y=@B

    ELSE IF @LOOP=3
    SET @Z=@B

    SET @LOOP=@LOOP+1
    END

    --CHANGE YOUR TABLE AND COLUMN NAME HERE.........
    --*****

    SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLENAME

    WHERE (COLUMN1=(CASE WHEN @X IS NULL THEN COLUMN1 ELSE @X END)
    or COLUMN2=(CASE WHEN @X IS NULL THEN COLUMN2 ELSE @X END)
    or COLUMN3=(CASE WHEN @X IS NULL THEN COLUMN3 ELSE @X END))

    and (COLUMN1=(CASE WHEN @Y IS NULL THEN COLUMN1 ELSE @Y END)
    or COLUMN2=(CASE WHEN @Y IS NULL THEN COLUMN2 ELSE @Y END)
    or COLUMN3=(CASE WHEN @Y IS NULL THEN COLUMN3 ELSE @Y END))

    and (COLUMN1=(CASE WHEN @Z IS NULL THEN COLUMN1 ELSE @Z END)
    or COLUMN2=(CASE WHEN @Z IS NULL THEN COLUMN2 ELSE @Z END)
    or COLUMN3=(CASE WHEN @Z IS NULL THEN COLUMN3 ELSE @Z END))

    --*****
    END


    --EXEC SAMPLESP '1,2'


    Regards,
    Sri

  • #730890
    Hi Sri,

    Our data is store in a single column there are no multiple columns. Like 1,2,3 are store in a single column. It can increase or decrease like the value can be like this:
    Case 1: 1,2
    Case 2: 1,2,3
    Case 3: 2
    Case 4: 1,2,3,5,4,6,7 etc.



    Regards
    Vivek Sharma

  • #730911
    Hi vivek,

    Here i post one new procedure based on your requirement, there is no possible way to check middle value so
    give multiple comma separated value like '1' to '1,2,3,4,5,6,7,8,9' and so on....
    but give first value it is very importent ie. if your have row like this
    1,2,3
    2,3,4
    1,2
    2
    1,2,3,4,5,6,7
    11,12,13
    11,13,14
    21,23,24
    21,25,26,27 then you must put first value ie. '1' or '2' or '11' or '21'
    check like '1,2' or '2,4' or '1,2,3,4,7' or '11,12' or '11,13'
    dont check with middle value like '26,27' or '13,14' etc

    check it and let me know.......

    Mark my post if helps....


    CREATE PROC SAMPLESP(@A VARCHAR(100))
    AS
    BEGIN


    ----***********************************Change your table and column name here only********************
    DECLARE @Column_Name VARCHAR(100)
    DECLARE @Table_Name VARCHAR(100)

    SET @Column_Name='Column1'
    SET @Table_Name='TEMP'
    ----**********************************************************************************************



    DECLARE @B VARCHAR(100)
    DECLARE @X VARCHAR(MAX),@Y VARCHAR(MAX),@Z VARCHAR(100),@LOOP INT
    CREATE TABLE #TEMP(Column1 VARCHAR(500))

    IF(CHARINDEX(',',@A,0)=0)
    BEGIN
    SET @Y=(' OR '+@Column_Name+' = '+''''+CONVERT(VARCHAR(200),@A)+'''')
    END
    ELSE
    SET @Y=''

    SET @A= @A+','
    SET @LOOP=1

    WHILE (CHARINDEX(',',@A,0)<>0)
    BEGIN
    SET @B=LEFT(@A,(CHARINDEX(',',@A,0)-1))
    SET @A=SUBSTRING(@A,(CHARINDEX(',',@A,0)+1),LEN(@A)-LEN(@B))
    IF @LOOP=1
    SET @B=@B+',%'
    ELSE
    SET @B='%,'+@B+'%'

    insert into #temp select @B
    SET @LOOP=@LOOP+1
    END

    SET @X=(SELECT 'Column1 LIKE '+''''+Column1+''''+' AND ' FROM #TEMP FOR XML PATH(''))

    SET @X=(SELECT SUBSTRING(@X,1,LEN(@X)-4))

    SET @X=('SELECT * FROM '+@Table_Name+' WHERE '+ @X + @Y )

    EXEC (@X)

    DROP TABLE #TEMP

    END

    /*

    EXEC SAMPLESP '2,3,5'

    */

    Regards
    Sri


  • Sign In to post your comments