question

sqltiger avatar image
sqltiger asked

How to easily parse a column that is more than four delimited values.

I am using SQL Server 2005 and have a column containing data that looks like this: text1>text2>text3>text4>text5>text6. The delimiter is ">". Each portion (text1, text2, etc.) could contain: 1 or 2 blank spaces (text - text, text & text), ampersand, n-dash, apostrophe + s, comma, period. Six is the maximum delimited values. Or, should that be five? Six values and five delimiters? There is no set length or position for each delimiter. I have used REPLACE to swap the ">" for a "." I would like to set up temp tables or column aliases, whichever is easiest. We refer to this data as levels, so I would like text1 = level1, text2 = level2, etc. I have found all sorts of options to handle this but they all seem unnecessarily involved/complicated. This data is quite simple and clean. Isn't there a simple and clean option to parse or split this data? Thank you so much!!
sql-server-2005replacesplitparsingdelimited-string
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
eghetto avatar image
eghetto answered
"Quick & Dirty" solution to give you an idea...: ; WITH data AS (SELECT 'text1>text2>text3>text4>text5>text6' AS D) , L1 AS (SELECT SUBSTRING(d, 0, CHARINDEX('>', data.D)) AS Level1 ,SUBSTRING(d, CHARINDEX('>', data.D) + 1, LEN(data.D)) AS Remaining FROM data) , L2 AS (SELECT SUBSTRING(L1.Remaining, 0, CHARINDEX('>', L1.Remaining)) AS Level2 ,L1.Remaining AS Remaining FROM L1) -- , L3 AS ( ... ) -- , L4 AS ( ... ) -- ... SELECT L1.Level1 ,L2.Level2 FROM L1 CROSS JOIN L2 -- CROSS JOIN L3 -- ...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

THANK YOU VERY MUCH eghetto! This did work for me. I was pulled off this problem - sorry it took me so long to let you know it worked. I do have another question for you though. How can I improve performace when running this? Is it even possible to do so?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.