---
title: "compare comma separated values in sql"  
description: "compare comma separated values in sql"  
author: "Anonymous User"  
published: 2013-07-16  
updated: 2013-07-17  
canonical: https://www.mindstick.com/forum/1295/compare-comma-separated-values-in-sql  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# compare comma separated values in sql

HI [developers](https://www.mindstick.com/articles/12875/blunders-you-must-avoid-while-hiring-java-developers-to-get-the-best-fulfilling-your-needs)!

I want to write a function for comparing [comma separated](https://www.mindstick.com/forum/159360/how-to-write-sql-query-to-join-comma-separated-values) values that will take two values (comma separated values) after [comparison](https://www.mindstick.com/articles/23182/comparison-maruti-suzuki-celerio-v-s-tata-tiago) the [return value](https://www.mindstick.com/forum/33675/how-to-convert-return-value-of-abmulivaluecopylabelatindex-into-nsstring-in-ios) will be [true or false](https://www.mindstick.com/forum/160329/how-can-you-convert-a-value-to-a-boolean-true-or-false-in-javascript)

What changes I have to do in this [SQL function](https://www.mindstick.com/forum/159813/what-is-an-sql-function-and-how-is-it-used-in-a-database-query) ?

This function is given below:-

I [am trying](https://answers.mindstick.com/qa/36834/which-two-programming-languages-should-i-master-in-if-i-am-trying-to-get-into-google-or-facebook) to write a function to compare comma separated [values in SQL](https://www.mindstick.com/forum/159818/how-to-use-the-coalesce-function-to-handle-null-values-in-sql-queries) I've taken some code from [Internet](https://www.mindstick.com/articles/44650/what-should-you-do-during-an-internet-outage) :

```
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  intASBEGIN    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

## Replies

### Reply by shreesh chandra shukla

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](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server)**

```
CREATE FUNCTION [dbo].[Split](       @s VARCHAR(max),    @split CHAR(1))RETURNS @temptable TABLE ([Item] VARCHAR(MAX))    ASBEGIN    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);RETURNEND;
```


---

Original Source: https://www.mindstick.com/forum/1295/compare-comma-separated-values-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.