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!
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Arifhusen AnsariConnect With a Mentor Business Intelligence AnalystCommented:
Hi,

I have attached the Scrip file with example.

We can do it using split function and pivot functionality. But you have to make sure that your "~" delimited data must have same number of column after parsing.

In may example i have pivoted two columns. If you want pivot more that two column you have to manually write the names of column.

Here is the snippet of the code you need to change.

SELECT * FROM #tmpData3
PIVOT 

(
MAX(splitdata)

FOR RowNum IN ([Col1],[Col2])

) P

Open in new window


I have used only ([Col1],[Col2]). You need to modify the statement like
([Col1],[Col2],[Col3],...) and so on with the number of column you want.

Please find the attached script file. I have also attached the script file for fnSplit.

Hope it will help you.
fnSplit.sql
Logic-For-Parsing.sql
0
 
PortletPaulCommented:
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
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
GREAT WORK,...PERFECT! This is exactly what I was looking for.

Many thanks!!
0
All Courses

From novice to tech pro — start learning today.