T-SQL function to split string with two delimiters as column separators into table

Question

I'm looking for a t-sql function to get a string like:

a:b,c:d,e:f

and convert it to a table like

ID     Value

a      b

c      d

e      f

Anything I found in Internet incorporated single column parsing (e.g. XMLSplit function variations) but none of them letting me describe my string with two delimiters, one for column separation & the other for row separation.

Can you please guiding me regarding the issue? I have a very limited t-sql knowledge and cannot fork those read-made functions to get two column solution?

Please note that it is OK for me to use any pair of seperators. If your solution involves using PARSENAME function which accepts dot as sepeator, don't hesitate to offer. — Farshid, Jul 30 '15 at 11:39
[Here](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) is a good answer for a split function. Note that you are still likely to have problems using values like this in an RDBMS anyways. It breaks First Normal Form. It's designed to be difficult to do what you're asking, and is essentially guaranteed to scale poorly. Note, too, that SQL Server 2016 will have JSON manipulation functions, but, like the XML functions, you should use them carefully because they're not intended to be an alternate to good relational design. — Bacon Bits, Jul 30 '15 at 11:47
A solution to similar question is provided here: http://stackoverflow.com/questions/17721723/sql-query-for-splitting-the-strings-into-rows-and-columns — Deepshikha, Jul 30 '15 at 11:48

score 1 · Accepted Answer · answered Jul 30 '15 at 11:39

1

You can find a split() function on the web. Then, you can do string logic:

select left(val, charindex(':', val)) as col1,
       substring(val, charindex(':', val) + 1, len(val)) as col2
from dbo.split(@str, ';') s(val);

answered Jul 30 '15 at 11:39

Gordon Linoff

1,242,037
58
646
786

Eralper · Answer 2 · 2015-07-30T15:06:59.773

You can use a custom SQL Split function in order to separate data-value columns Here is a sql split function that you can use on a development system It returns an ID value that can be helpful to keep id and value together

You need to split twice, first using "," then a second split using ";" character

declare @str nvarchar(100) = 'a:b,c:d,e:f'
select
    id = max(id),
    value = max(value)
from (
select 
    rowid,
    id = case when id = 1 then val else null end,
    value = case when id = 2 then val else null end
from (
select 
    s.id rowid, t.id, t.val
from (
    select * from dbo.Split(@str, ',')
) s
cross apply dbo.Split(s.val, ':') t
) k
) m group by rowid

T-SQL function to split string with two delimiters as column separators into table

2 Answers2