Loading

SQL query to compare comma separated values in order by manner.

SQL query to compare comma separated values in order by manner.

Vishal Joshi

Vishal Joshi

3y980SQL Server

Have data in the database like the below table.

ID	Data
1	4963,4960
2	4961,4963

User input values can be like 4963,4960 or 4960,4963 both should match.

Please share the SQL query which matches both cases.

Thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana

Naimish Makwana3y ago

Accepted answer

Hello Vishal,

Try below

SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY CAST(value AS INT)) FROM ( SELECT value FROM STRING_SPLIT('4963,4960,4962,4961', ',') ) AS t

Thanks

Naimish Makwana

Vishal Joshi3y ago
Hello Naimish and Henry,

Thanks for the answers. Naimish answer matched my requirement.

Henry your answer also matches but we need to create a dynamic query based on user input.

Again thank you so much.
horald Henry3y ago
To find comma-separated values in a string, you can use FIND_IN_SET() function. Here's a sample query that should work in both scenarios:
```
SELECT * FROM table_name
WHERE 
(FIND_IN_SET('4963', Data) > 0 AND FIND_IN_SET('4960', Data) > 0) OR
(FIND_IN_SET('4960', Data) > 0 AND FIND_IN_SET('4963', Data) > 0)
```
Using the FIND IN SET function, this query first determines if the values '4963' and '4960' are present in the 'Data' column. The first criterion will be met if they are present. If not, it uses the second condition to check the reverse order '4960' and '4963'.