Create a table from double delimited string in stored procedure

Question

Given an input value to a Stored procedure such as the following:

264#20,241#15,228#10

How could I convert this into a a table as below?

ID1     ID2
264     20
241     15
228     10

Thanks

Here is a [post](http://stackoverflow.com/questions/13913173/splitting-delimited-values-in-a-sql-column-into-multiple-rows-that-uses-multiple) for you to get **an another idea**. — bonCodigo, Dec 18 '12 at 15:29

score 0 · Accepted Answer · answered Dec 18 '12 at 15:25

You could use something like this in a stored procedure:

;with cte (item, col1) as
(
  select 
    cast(left(col1, charindex(',',col1+',')-1) as varchar(50)) item,
         stuff(col1, 1, charindex(',',col1+','), '') col1
  from yourtable
  union all
  select 
    cast(left(col1, charindex(',',col1+',')-1) as varchar(50)) item,
    stuff(col1, 1, charindex(',',col1+','), '') col1
  from cte
  where col1 > ''
),
s2 (id1, id2) as
(
  select substring(item, 1, charindex('#', item)-1), 
    reverse(substring(reverse(item), 1, charindex('#', reverse(item))-1))
  from cte
)
select id1, id2
from s2

See SQL Fiddle with Demo

Result:

| ID1 | ID2 |
-------------
| 264 |  20 |
| 241 |  15 |
| 228 |  10 |

Create a table from double delimited string in stored procedure

1 Answers1