troubleshooting Question

How to PARSE a text field that is delimited by '~' character?

Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
3 Comments1 Solution235 ViewsLast Modified:
My data looks like this:

RecordID, NewDetailData
7,1P0007.63579.0000~1609168541~899972053~1230~CN25~2P201608232016082399348~1~0~1~51~401.9~52~719.45~71609168541
11,1P0007.63512.0000~1093765679~100458751~1230~CN25~2P201608262016082699347~1~0~1~51~787.01~71093765679
21,1P0014.236945.0000~1174807291~2254912701~1230~GW23~2P201608252016082599341~1~0~1~51~402.10~52~272.2~53~250.00~54~244.9~55~414.01~56~278.02~57~V85.24~71174807291~1P0003.316952.0000~1962840793~A00199273~3230~MM05

I need a SQL Script that will allow me to Parse out the different FIELDS that are represented by the data elements separated by the '~' character. Not the dash but the "squiggly line" character.

I need the result set like this:

RecordID,Col1, Col2, Col3, Col4 ..etc...etc..
7,1P0007.63579.0000,1609168541,899972053,1230,CN25,2P201608232016082399348,1,0,1,51,401.9,52,719.45,71609168541
11,1P0007.63512.0000,1093765679,100458751,1230,CN25,2P201608262016082699347,1,0,1,51,787.01,71093765679
21,1P0014.236945.0000,1174807291,2254912701,1230,GW23,2P201608252016082599341,1,0,1,51,402.10,52,272.2,53,250.00,54,244.9,55,414.01,56,278.02,57,V85.24,71174807291,1P0003.316952.0000,1962840793,A00199273,3230,MM05

THANKS!
ASKER CERTIFIED SOLUTION
Arifhusen Ansari
Business Intelligence Developer and Analyst
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros