CODEWITHSUNDEEP
sql-servert-sql
Stefan Wolf
Stefan Wolf

Reputation: 45

Selecting separated data from string as singe rows in MSSQL

I have a table of a legacy system with structure and data like this:

id          data

1   some|data|I

2   need|to|get

3   out

4   of|the|database

I need some way to get this data as single rows in a query like so:

some

data

I

need

to

get

...

and so on.

Anyone got an idea on how to do that?

Thanks a lot!

Upvotes: 1

Views: 48

Answers (1)

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table1
    ([id] int, [data] varchar(15))
;

INSERT INTO #Table1
    ([id], [data])
VALUES
    (1, 'some|data|I'),
    (2, 'need|to|get'),
    (3, 'out'),
    (4, 'of|the|database')
;
select value from #table1 cross apply (select value from string_split([data],'|'))A

output 

value
some
data
I
need
to
get
out
of
the
database

Upvotes: 3

Related Questions