Split string into table given row delimiter and column delimiter in SQL server
split, sql, sql-server, string
Solution
One of the easier ways is to convert the string to XML based on replacing your delimiters.
declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)
SELECT
t.n.value('col[1]','CHAR(1)') as Col1
,t.n.value('col[2]','CHAR(1)') as Col2
,t.n.value('col[3]','CHAR(1)') as Col3
FROM
@xmlstr.nodes ('/rows/row') AS t(n)
- Format string as XML `<rows><row><col></col><col></col></row><row><col></col><col></col></row></rows>` Basically you need to add the beginning and ending tags then replace the column delimiter with the column tags and the row delimiter with both column and row tags
- .nodes is a method on the xml data type that "is useful when you want to shred an xml data type instance into relational data" https://msdn.microsoft.com/en-us/library/ms188282.aspx
- `as t(n)` tells you how you will end up accessing the XML row and column. t being the table alias and n being the node alias (kind of like a row). so t.n.value() gets a particular row
- `COL[1]` means get the first `COL` tag in the row it is 1 based so 2 is the next then 3 etc.
- `CHAR(1)` is a datatype definition meaning 1 character and was based on your example data having only 1 character per column. you may noticed I made it `VARCHAR(MAX)` in the dynamic query because if data type is unknown then you will want more flexibility.
Or dynamically
DECLARE @str varchar(max)='A,B,C,D,E;F,G,H,I,J;K,L,M,N,O';
DECLARE @NumOfColumns INT
SET @NumOfColumns = (LEN(@str) - LEN(REPLACE(@str,',',''))) / (LEN(@str) - LEN(REPLACE(@str,';','')) + 1) + 1
DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)
DECLARE @ParameterDef NVARCHAR(MAX) = N'@XMLInputString xml'
DECLARE @SQL NVARCHAR(MAX) = 'SELECT '
DECLARE @i INT = 1
WHILE @i <= @NumOfColumns
BEGIN
SET @SQL = @SQL + IIF(@i > 1,',','') + 't.n.value(''col[' + CAST(@i AS VARCHAR(10)) + ']'',''NVARCHAR(MAX)'') as Col' + CAST(@i AS VARCHAR(10))
SET @i = @i + 1
END
SET @SQL = @SQL + ' FROM
@XMLInputString.nodes (''/rows/row'') AS t(n)'
EXECUTE sp_executesql @SQL,@ParameterDef,@XMLInputString = @xmlstr
Problem
How to split string containing matrix into table in SQL Server? String has columns and row delimiters. Suppose I have a string: ``` declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z'; ``` Expected results (in three separate columns): ``` +---+---+---+ | A | B | C | +---+---+---+ | D | E | F | +---+---+---+ | X | Y | Z | +---+---+---+ ``` I am looking for general solution which has not defined number of columns and rows. So the string: ``` declare @str varchar(max)='A,B;D,E'; ``` will be split into table with two columns: ``` +---+---+ | A | B | +---+---+ | D | E | +---+---+ ``` My efforts. My first idea was to use dynamic SQL which turns the string into: `insert into dbo.temp values (...)` This approach although very fast has a minor drawback because it requires creating a table with the right number of columns first. I have presented this method in the answer to my own question below just to keep the question short. Another idea would be to write down the string to a CSV file on the server and then `bulk insert` from it. Though I do not know how to do it and what would be performance of first and second idea. The reason why I asked the question is because I want to import data from Excel to SQL Server. As I have experimented with different ADO approaches, this method of sending matrix-string is a landslide victory, especially when the length of the string increases. I asked a younger twin brother of the question here: Turn Excel range into VBA string where you will find suggestions how to prepare such a string from Excel range. Bounty I decided to award Matt. I weighed highly Sean Lange's answer. Thank you Sean. I liked Matt's answer for its simplicity and shortness. Different approaches apart from Matt's and Sean's could be in parallel use so for the time being I am not accepting any answer (update: Finally, after a few months, I have accepted Matt's answer). I wish to thank Ahmed Saeed for his idea with VALUES, for it is a nice evolution of the answer I began with. Of course, it is no match for the Matt's or Sean's. I upvoted every answer. I will appreciate any feedback from you on using these methods. Thank you for the quest.