Compare Comma Separated Values in SQL

 

Introduction

In this article, we will see how we can compare the comma-separated values with existing values stored in the table. Values can be in a different order from the user or table. We have to first order it both sides and then compare them.

Create a Table and Insert the Data

Here I create a table with only one column which contains values with commas. And insert 4 values as of now.

CREATE TABLE COMMA_SEPERATE_VALUES
(
    ITEM VARCHAR(500) NOT NULL
)
INSERT INTO COMMA_SEPERATE_VALUES VALUES
('123,456,789'),
('ABC,XYZ,PQR'),
('321,654,987'),
('ZXY,BCA,QRP')

SQL

But before starting the Query for comparing values, we must clarify the following concepts.

String_split Function

The STRING_SPLIT() function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator.

Syntax 

STRING_SPLIT ( input_string , separator )

SQL

• input_string is a string in which we want to perform the operation. The input string must be varchar,nvarchar, nchar, or char.
• Separator is a single character value using which we want to split the string.

SELECT [VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE]

SQL

When we execute the above Query, it will return a table with values in ascending order, as shown below.

FOR XML PATH 

In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply placing the FOR XML clause at the end of the Query will output the results in XML.

But more importantly, it will return data in a single row no matter if you have n numbers of the data row.

SELECT [VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH

SQL

Executing the above Query will return output as shown in the image below.

As you can see in the above image, the output has the main row element and then the element of the column name. But for this use case, we don’t need that. We can also rename the row root tag name by specifying the name after the path.

SELECT [VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH('')

SQL

The above Query will return output as shown in the below image, but still, it comes with a column name as an element, but we don’t need that. We need values with commas. To get this type of output, we can use the below Query.

SELECT ','+[VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH('')

SQL

In the above Query, we are Concating column value after the comma and not specifying any column name. So it will not generate any tag for this column, and we will get the output as shown below.

STUFF Function

The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.

Syntax

STUFF(string, startIndex, length, new_string)

SQL

• string: The string to be modified
• startIndex: The position in the string to start to delete some characters
• length: The number of characters to delete from the string
• new_string:  The new string to insert into the string at the start position

SELECT STUFF('YOGESH HADIYA',1,6,'KISHOR')

SQL

In the above Query, it will start replacing from 1st index to 6 characters. So as you can see below the image, it will replace YOGESH with KISHOR.

SELECT STUFF((SELECT ','+[VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH('')),1,1,'')

SQL

The above Query will return a string by removing the first comma from the string so we can use it in our comparison.

Compare Comma Separate String With Table

In our table, there are 4 records, as you can see in the below image.

SELECT * FROM COMMA_SEPERATE_VALUES

SQL

Now we want to check if any record contains these 456,123,789 Values.

SELECT * FROM COMMA_SEPERATE_VALUES
WHERE
STUFF((SELECT ','+[VALUE] FROM string_split(ITEM,',') ORDER BY [VALUE] ASC FOR XML PATH('')),1,1,'')
=STUFF((SELECT ','+[VALUE] FROM string_split('456,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH('')),1,1,'')

SQL

In the above Query, we check both sides of the string by equal to the operator. 

In the where clause, we used the same functions discussed above.

The left-hand side value comes from the table, and the right side comes from the user’s input.

As you can see in the below image, it will return output with the matched record.

SQL

SELECT * FROM COMMA_SEPERATE_VALUES
WHERE
STUFF((SELECT ','+[VALUE] FROM string_split(ITEM,',') ORDER BY [VALUE] ASC FOR XML PATH('')),1,1,'')
=STUFF((SELECT ','+[VALUE] FROM string_split('820,123,789',',') ORDER BY [VALUE] ASC FOR XML PATH('')),1,1,'')

SQL

We don’t have any record with 820,123,789 value, so it will not return any record from the table.

I hope you find this article helpful. If you have any doubts, you can ask in the comments below.

Disable checkingPremium suggestions