How to split a string from a table SQL Server 2012?

Question

I'd like to split comma-delimited strings in SQL Server 2012. I'm interested in an XML solution, not a function or while loop (performance and permissions reasons). I read this post: STRING_SPLIT in SQL Server 2012 which was helpful, however, my context is not splitting a variable but rather a column in a table. Below is an example of the kind of dataset I'm working with:

CREATE TABLE #EXAMPLE 
(
    ID INT,
    LIST VARCHAR(1000)
)

INSERT INTO #EXAMPLE
VALUES (1, '12345,54321'), (2, '48965'), (3, '98765,45678,15935'), (4, '75315')

SELECT * FROM #EXAMPLE

DROP TABLE #EXAMPLE

Given that dataset, how could I go about splitting the LIST field on the comma so that I get this data set?

CREATE TABLE #EXAMPLE 
(
    ID INT,
    LIST VARCHAR(1000)
)

INSERT INTO #EXAMPLE
VALUES (1, '12345'), (1, '54321'), (2, '48965'), (3, '98765'), (3, '45678'), (3, '15935'), (4, '75315')

SELECT * FROM #EXAMPLE

DROP TABLE #EXAMPLE

I feel like I'm blanking on implementing this with a table column as opposed to a variable, but I'm sure it's pretty similar. I'd be greatly appreciative of any input. Thanks!

Stu · Answer 1 · 2021-11-05T22:29:40.300

2

If you want an XML solution the following should hopefully suffice.

Note - this is easily wrapped in a reusable table-valued function however you state you don't want a function so just using in-line.

select e.id, s.List
from #example e
    cross apply (
      select List = y.i.value('(./text())[1]', 'varchar(max)')
      from ( 
        select x = convert(xml, '<i>' + replace(e.list, ',', '</i><i>') + '</i>').query('.')
      ) as a cross apply x.nodes('i') as y(i)
)s

See working Fiddle

edited Nov 05 '21 at 22:29

answered Nov 05 '21 at 22:02

Stu

30,392
6
14
33

1

Missed the "not a function" +1 – John Cappelletti Nov 05 '21 at 23:15
You have a risk of XML errors, so better to use `select x = cast(replace(cast(cast('' as xml).query('element i {sql:column("e.list")}') as nvarchar(max)), ',', '') as xml)` see fiddle https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=2fffd7f8fe66e174bc8e66013be09585. Either way `.query('.')` is not necessary – Charlieface Nov 06 '21 at 19:06
I usually add a CDATA section for such scenario. – Yitzhak Khabinsky Nov 07 '21 at 00:22

score 1 · Accepted Answer · answered Nov 05 '21 at 22:30

1

Taking into account your link, this can be done by slightly changing the query by adding Cross Apply.

Select e.ID, t.a
From #Example As e Cross Apply ( 
SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST('<X>'+REPLACE(e.List, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) As t(a)

answered Nov 05 '21 at 22:30

Anton Grig

1,640
7
11

Thanks, Anton, this gets me what I'm looking for and is very similar to the other query. I did have to modify it slightly though to get it to work. – JD136 Nov 08 '21 at 18:29

score 0 · Answer 3 · answered Nov 07 '21 at 00:35

As @Charlieface already mentioned there is a risk to bump into XML entities: ampersand and the like.

That's why I always use a CDATA section for safety.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT, LIST VARCHAR(1000));
INSERT INTO @tbl VALUES
(1, '12345,<54321'),
(2, '48965'),
(3, '98765,45678,15935'),
(4, '75315');
-- DDL and sample data population, end

SELECT e.id, s.List
FROM @tbl e
    CROSS APPLY (
    SELECT List = y.i.value('(./text())[1]', 'VARCHAR(MAX)')
    FROM ( 
        SELECT x = TRY_CAST('<i><![CDATA[' + REPLACE(e.list, ',', ']]></i><i><![CDATA[') + ']]></i>' AS XML)
      ) AS a CROSS APPLY x.nodes('i') as y(i)
) AS s;

Output

+----+--------+
| id |  List  |
+----+--------+
|  1 | 12345  |
|  1 | <54321 |
|  2 | 48965  |
|  3 | 98765  |
|  3 | 45678  |
|  3 | 15935  |
|  4 | 75315  |
+----+--------+

How to split a string from a table SQL Server 2012?

3 Answers3