CODEWITHSUNDEEP
sql-servert-sql
JD136
JD136

Reputation: 175

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

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!

Upvotes: 1

Views: 930

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

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  |
+----+--------+

Upvotes: 0

Anton Grig
Anton Grig

Reputation: 1719

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)

Upvotes: 1

Stu
Stu

Reputation: 32614

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

Upvotes: 2

Related Questions