[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2016-09-04
3
Medium Priority
?
106 Views
Last Modified: 2016-09-05
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!
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41784248
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
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 2000 total points
ID: 41784261
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
 
LVL 17

Author Closing Comment

by:MIKE
ID: 41784838
GREAT WORK,...PERFECT! This is exactly what I was looking for.

Many thanks!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question