sql i need to parse text without creating a function [duplicate]

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
– Academiphile, Commented Jan 2, 2016 at 19:10
can the delimiter be space or semi-colon or any other character ? What is the database? — minatverma
– minatverma, Commented Jan 2, 2016 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
– Steve, Commented Jan 2, 2016 at 19:24

Gordon Linoff · Accepted Answer · 2016-01-02 19:53:16Z

1

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.

answered Jan 2, 2016 at 19:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql i need to parse text without creating a function [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related