compare comma separated values in sql

Total Post:96

Points:676
 1598  View(s)
Ratings:
Rate this:
HI developers!

I want to write a function for comparing comma separated values that will take two values (comma separated values) after comparison the return value will be true or false

What changes I have to do in this SQL function ?

This function is given below:-

I am trying to write a function to compare comma separated values in SQL I've taken some code from Internet :

SELECT CASE WHEN EXISTS
(
  SELECT 1 FROM dbo.Split(@v1)
  WHERE ', ' + LTRIM(@v2) + ','
  LIKE '%, ' + LTRIM(Item) + ',%'
) THEN 1 ELSE 0 END;

Then I make a function :

CREATE FUNCTION [dbo].[fnCompareCSVString]
(  

    @str1 nvarchar(50),
    @str2 nvarchar(50)
)
 
RETURNS  int
AS
BEGIN
    SELECT CASE WHEN EXISTS
    (
       SELECT 1 FROM dbo.Split(@str1)
       WHERE ', ' + LTRIM(@str2) + ','
         LIKE '%, ' + LTRIM(Item) + ',%'
    ) THEN 1 ELSE 0 END;
END

I am not good in SQL I know this is wrong

Thanks in advance 

  1. Post:105

    Points:735
    Re: compare comma separated values in sql

    solution!

    Is this what you are looking for?

    True / False results

    -- matches only those values which exist in both CSV sets
    
    SELECT T1.[Item], CASE WHEN T2.[Item] IS NULL THEN 0 ELSE 1 END AS [Match]
    FROM [dbo].[Split]('val1,val2,val3', ',') AS T1
        LEFT JOIN [dbo].[Split]('val3,val4', ',') AS T2 on T1.[Item] = T2.[Item]

    Returns

    Item    Match
    
    val1 0
    val2 0
    val3 1

    Only true matches

    -- matches only those values which exist in both CSV sets
    
    SELECT T1.[Item]
    FROM [dbo].[Split]('val1,val2,val3', ',') AS T1
        INNER JOIN [dbo].[Split]('val3,val4', ',') AS T2 on T1.[Item] = T2.[Item]

    Returns

    Item
    val3

    Split function

    CREATE FUNCTION [dbo].[Split]
    (  

        @s VARCHAR(max),
        @split CHAR(1)
    )
    RETURNS @temptable TABLE ([Item] VARCHAR(MAX))   
    AS
    BEGIN
        DECLARE @x XML
     
        SELECT @x = CONVERT(xml,'<root><s>' + REPLACE(@s,@split,'</s><s>') + '</s></root>');
     
        INSERT INTO @temptable         
        SELECT [Value] = T.c.value('.','varchar(20)')
        FROM @X.nodes('/root/s') T(c);
    RETURN
    END;

      Modified On Mar-30-2018 05:11:21 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!