sql-server - 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!

标签： sql-servertsql

Answer 1

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