Query Comma Seperated List using SQL XML
Using SQL XML query on SQL Server database, developers can search for specific values in a comma seperated list stored in a database table column.
Instead of splitting string using SQL split functions, either using string_split or user-defined SQL split function, SQL database developer can query concatenated string value using SQL XML.
Although there are certainly other solutions better in performance, this SQL tutorial shows how comma seperated string value lists (concatenated strings seperated with comma, etc) can be converted into XML data type and queried with SQL Server XML functions.
Assume that in your SQL database you create a table to store authors of each book.
The identity column values (or Primary Keys) of authors will be kept in AuthorIdList in a concatenated way so that if there are more than one author, the co-authors will be seperated by "," comma from each other.
Here is a part of my data model for this SQL requirement on SQL Server. Please ignore that I did not share Authors table here. We will not require such details for this tutorial.
create table Books (
Id int, BookName nvarchar(255), AuthorIdList varchar(100)
)
Now let's populate Books table with some test data.
insert into Books select 1,'Learning SQL','1,2,3'
insert into Books select 2,'Advanced SQL','11,2,31'
insert into Books select 3,'SQL Fundamentals','12,21,13'
insert into Books select 4,'SQL Reference','23,31,3'
insert into Books select 5,'Database Development','23,31,3'
Now let's assume that you are looking for the list of books that a specific author has written.
Let's query for author id 2.
Please note that below query is not a correct way of searching for an ID in a comma seperated or concatenated list of values.
select * from Books where AuthorIdList like '%2%'
A filtering error has occured because the output of the above SQL query listed 12, 21 and 23 as well as targeted value 2.
Let's modify our query to make it more wise by adding commas around the target author's id value.
select * from Books where AuthorIdList like '%,2,%'
Nice above query worked successfully for Id 2, but what would it result if we search for author id equal to 1.
Let's execute the same SQL query for 1
select * from Books where AuthorIdList like '%,1,%'
The SQL query did not bring any row data. Above database query failed for first row because the list starts with "1" not with comma as we searched for ",1,".
In fact the solution is easy in this case. Let's add comma characters to the start and end of the Id list column as in following SQL Select query
select * from Books where ',' + AuthorIdList + ',' like '%,1,%'
I know, this works successfully with expected result set. But what about performance?
Using a column in a function like we did above will prevent use of index losing from performance.
But it still can be used for most cases.
Let's split the concatenated string value using string_split SQL function introduced with SQL Server 2016 for database programmers as follows.
select Books.* from Books
cross apply STRING_SPLIT(AuthorIdList, ',') as Author
where Author.[value] = '1'
As seen in above query, database table data is selected then using CROSS APPLY the table-valued function string_split returns one row for each splitted Id value. The filtering criteria in WHERE clause is directly the searched author's Id value.
Of course this method, splitting comma seperated list can be applied to the same solution by using a custom SQL split function.
select Books .* from Books
cross apply dbo.split(AuthorIdList, ',') as Author
where Author.val = '1'
Now, I want to introduce another method to solve this SQL problem.
Following SQL CONVERT function used with REPLACE procudes a column with XML data type
SELECT
Id, BookName,
convert(xml, '<root><id>' + REPLACE(AuthorIdList, ',', '</id><id>') + '</id></root>') as list
FROM Books
This XML field can be splitted into its nodes as follows
select
Id, BookName, sqlXML.value('.','varchar(5)') as AuthorList
from (
SELECT
Id, BookName,
convert(xml, '<root><id>' + REPLACE(AuthorIdList, ',', '</id><id>') + '</id></root>') as list
FROM Books
) tbl
CROSS APPLY list.nodes('/root/id') as XMLData(sqlXML)
WHERE sqlXML.value('.','varchar(5)') = '2'
In fact, this SQL XML query does not differ from splitting string using SQL functions.
On the other hand, below SQL XML query where data is filtered with SQL Server XML functions like EXIST() is more advanced
with xmlData as (
SELECT
Id, BookName, AuthorIdList,
convert(xml, '<root><id>' + REPLACE(AuthorIdList, ',', '</id><id>') + '</id></root>') as list
FROM Books
)
SELECT Id, BookName, AuthorIdList, list
FROM xmlData
WHERE list.exist('(/root/id[.="3"])') = 1
