sql i need to parse text without creating a function

Question

I do not have the access needed to create a function; but I need to be able to parse the following text.

ID           value
684286211   96756263;97051390
683855568   96825924;96862563;96862652;96862684;96862692

needing:

ID               value
684286211       96756263 
684286211       97051390
683855568       96825924
683855568       96862563
683855568       96862652
683855568       96862684
683855568       96862692

I have tried using the Parsename statement but it only works on 4 or less sections; I need to allow up to nine values.

using sql 2012

any help would be appreciated!

What language are you using? How is your sql table set up? You need to give us more info. — Academiphile, Jan 02 '16 at 19:10
can the delimiter be `space` or `semi-colon` or any other character ? What is the database? — minatverma, Jan 02 '16 at 19:14
I need to be able to parse in sql;it is a semicolon, however I can replace it with whateveris needed. — Steve, Jan 02 '16 at 19:24
You shouldn't store delimited values in a single column. It would be better if you fixed your data model — , Jan 02 '16 at 20:04

score 1 · Accepted Answer · answered Jan 02 '16 at 19:53

You can use a recursive CTE for this purpose:

with CTE as (
      select id, value,
             left(value, charindex(';', value)) as val,
             substring(value, charindex(';', value) + 1, len(value))+';' as restval
     from t
     where value like '%;%'
     union all
     select id, value, left(restval, charindex(';', restval)) as val,
            substring(restrval, charindex(';', restval) + 1, len(restval))
     from cte
     where value like '%;%'
    )
select id, val
from cte
union all
select id, value
from t
where value not like '%;%';

It is a pain to avoid errors for values that have no semicolons. I think this is one method for doing this.

sql i need to parse text without creating a function

1 Answers1