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

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!

CodePudding user response：

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

CodePudding user response：

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)