CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
deep
deep

Reputation: 41

extract data from pipe delimited String

Hi i have a table where the data is stored in string format which is pipe delimited , now i need to extract data from string and create columns. sample of the data which is stored as string is : 

"<U|'3526'|2|1|EMAIL_ID|[email protected], [email protected]>"

now i need to fetch data like below :

U '3526' 2 1 EMAIL_ID kaye.warne@gradusworld.com, melanie.farnan@corptraveller.co.uk

Can any one help me with this please. Thanks

Upvotes: 0

Views: 1933

Answers (1)

Abhishek
Abhishek

Reputation: 2490

There are whole lot many articles out there explaining how to perform string splits, recommend to read them one for example is this.

One way to achieve what you ask is below -

DECLARE @S varchar(max),
        @Split char(1),
        @X xml
SELECT @S = '<U|''3526''|2|1|EMAIL_ID|[email protected], [email protected]>',
       @Split = '|'
SELECT @S = REPLACE(REPLACE(@S,'<',''),'>','')
SELECT @X = CONVERT(xml,' <root> <myvalue>' + REPLACE(@S,@Split,'</myvalue> <myvalue>') + '</myvalue>   </root> ')

SELECT DISTINCT 
  T.c.value('(/root/myvalue)[1]','VARCHAR(20)')  ,
  T.c.value('(/root/myvalue)[2]','VARCHAR(20)'),
  T.c.value('(/root/myvalue)[3]','VARCHAR(20)'),
  T.c.value('(/root/myvalue)[4]','VARCHAR(20)'),
  T.c.value('(/root/myvalue)[5]','VARCHAR(20)'),
  T.c.value('(/root/myvalue)[6]','VARCHAR(2000)')
 FROM @X.nodes('/root/myvalue') T(c)

Upvotes: 4

Related Questions