Link to home
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

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

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!
Avatar of PortletPaul
PortletPaul
Flag of Australia image

REPLACE ( string_expression , string_pattern , string_replacement )  

e.g.
              replace([yourtable].[yourcolumn]  ,'~'  ,   ','   )

tilde
ˈtɪldə/Submit
noun
an accent (~) placed over Spanish n when pronounced ny (as in señor ) or Portuguese a or o when nasalized (as in São Paulo ), or over a vowel in phonetic transcription, indicating nasalization.
a symbol similar to a tilde used in mathematics and logic to indicate negation, inversion, etc.
https://www.google.com.au/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=define:tilde
ASKER CERTIFIED SOLUTION
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Marcus Aurelius

ASKER

GREAT WORK,...PERFECT! This is exactly what I was looking for.

Many thanks!!