Comparing Two Comma-Separated CSV Values in SQL

Learn how to compare two comma-separated values in SQL and retrieve matching values effortlessly. Suitable for SQL Server users, even without advanced functions!
---
This video is based on the question https://stackoverflow.com/q/67322479/ asked by the user 'user15795435' ( https://stackoverflow.com/u/15795435/ ) and on the answer https://stackoverflow.com/a/67323282/ provided by the user 'Yitzhak Khabinsky' ( https://stackoverflow.com/u/1932311/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to compare two comma-separated value in a column, and return only matching values in SQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Compare Two Comma-Separated Value Columns in SQL

When working with SQL databases, you might find yourself needing to compare two columns filled with comma-separated strings. This is particularly useful in scenarios where you want to identify common values between them. In this guide, we’ll dive into a common problem experienced by SQL Server users and provide a clear solution using XQuery to accomplish this task effectively.

The Problem

Imagine having two columns in your database with comma-separated values like this:

Column 1: John, james, steve

Column 2: john, smith, will, james

Your goal is to extract the overlapping values from both columns. For the example above, the intended output would yield John,james because these names exist in both columns, regardless of case sensitivity.

The challenge here is that, in SQL Server 2012, you cannot use the String_Split function to handle these comparisons easily. But don't worry; we’ll explore a neat alternative using XQuery.

The Solution

We can leverage the capabilities of XQuery and its FLWOR (For, Let, Where, Order by, Return) expression to achieve our goal efficiently. Below, I will provide a detailed SQL script that illustrates this process.

Step-by-step SQL Script

Define the Table Structure and Insert Sample Data:
We start by declaring a table variable that simulates our dataset with two columns containing the comma-separated values.

[[See Video to Reveal this Text or Code Snippet]]

Setting Up the Separator:
Next, we define a variable for our separator. In this case, it’s the comma followed by a space.

[[See Video to Reveal this Text or Code Snippet]]

The Select Query Using XQuery:
Now comes the main part. We perform the select operation which utilizes XQuery to find matching names from the two columns.

[[See Video to Reveal this Text or Code Snippet]]

The Output

Running the above script will yield results like the following.

[[See Video to Reveal this Text or Code Snippet]]

As you can see, the output precisely captures the matching values John,james and Mary, fulfilling our initial requirement.

Conclusion

Comparing two columns with comma-separated values in SQL may seem challenging, especially when dealing with older SQL Server versions. However, by utilizing XQuery and the script provided, you can efficiently retrieve matching values regardless of their case formatting. This approach not only solves the problem but also enhances your SQL querying skills, arming you with powerful techniques for future projects.

Now you are ready to tackle similar SQL challenges with confidence! Happy querying!

Видео Comparing Two Comma-Separated CSV Values in SQL канала vlogize