compare comma separated values in sql
10,441
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;
Comments
-
Kranti Singh almost 2 years
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
Question:
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 ?
-
Kranti Singh almost 11 yearsI want split ,compare then return true or false in one function I want to use this function in stored procedure
-
Kranti Singh almost 11 yearsI will pass two parameter as str1(comma separated values) and str2 (comma separated values)
-
Kranti Singh almost 11 yearsHi Kane , thanks for help ....This will help me but According to our requirement I cant modify your code because I am not good in SQL .... In my Stored procedure I have to compare two CSV string then if any one is matching then return true else false ......So I want to write a function ....As I written in my Question
-
Kranti Singh almost 11 yearsThis is working fine ...Can any one convert this SP in SQL function and return true or false ( now returning matching word)