SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help to split Data


Need help to split Data

Author
Message
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47917 Visits: 10844
Sean Lange (12/19/2013)
abhas (12/19/2013)
Hi,
Sorry Sowbhari,

its working.


DECLARE @StartTime VARCHAR(MAX),@X XML
SELECT @StartTime = '7:00 AM <br/> 12:30 PM'
SELECT @X = CONVERT(XML,'
' + replace(@StartTime,'<br/> ','</br>
') + '</br>')
SELECT x.i.value('.','varchar(max)') AS [StartTime]
FROM @X.nodes('//br') x(i)


Thanks.


I would again recommend you look at the articles I referenced. They will perform a LOT better in most situations. Try them out and see what works for you but the DelimitedSplit8K function is crazy fast.

+1 +1 +1 This function performs better than any other string splitting function I've seen and performance is very linear as you run it with larger data sets. It requires a bit of stretching at first, but once you "get it" it'll change the way you look at data. To test it out for yourself, run both solutions up to 1,000,000 rows and compare performance.

When you're done splitting things up, you may want to consider addressing the root cause - an application (or load process) putting two values in one column. Can the user enter 3 values? How about 17 values? Once the application is fixed, you can fix the data that's there.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17539 Visits: 6431
Sean Lange (12/19/2013)
Everyone on this thread should take a look at the link in my signature for splitting strings. In there you will find a number of ways to split a string including the while loop and xml methods. As you continue reading you will find a tally table set based splitter. It will blow the doors off all the above methods for performance.

Another method that is super duper fast is Dwain Camp's pattern splitter. You can find it here. http://www.sqlservercentral.com/articles/String+Manipulation/94365/


I appreciate the vote of confidence Sean, but I think in this case I'd opt for something simpler like this:


WITH SampleData (StartTime) AS
(
SELECT '7:00 AM <br/> 12:30 PM'
UNION ALL SELECT '11:00 AM <br/> 2:30 PM'
)
SELECT StartTime, StartTimes
FROM SampleData a
CROSS APPLY
(
SELECT REPLACE(StartTime, ' <br/> ', ' ')
) b (st)
CROSS APPLY
(
VALUES(LEFT(st, CHARINDEX('M', st))),(STUFF(st, 1, CHARINDEX('M', st)+1, ''))
) c (StartTimes)




The CROSS APPLY VALUES approach to UNPIVOT is explained in the first article in my signature links.

BTW. Last name is "Camps" so possessive would be Camps' :-D


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61807 Visits: 17954
Last name is "Camps" so possessive would be Camps' :-D


Doh!!! Sorry about getting your name wrong. Blush

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212357 Visits: 41977
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps' :-D


Doh!!! Sorry about getting your name wrong. Blush


Dwain must have been "camping out" just waiting for you to make that mistake. I know, I know... you've both had mo-den enough of those kinds of jokes. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212357 Visits: 41977
Ed Wagner (12/19/2013)
+1 +1 +1 This function performs better than any other string splitting function I've seen and performance is very linear as you run it with larger data sets. It requires a bit of stretching at first, but once you "get it" it'll change the way you look at data. To test it out for yourself, run both solutions up to 1,000,000 rows and compare performance.


Doesn't matter how nice your are, Ed, it's still your turn to buy lunch. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17539 Visits: 6431
Jeff Moden (1/1/2014)
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps' :-D


Doh!!! Sorry about getting your name wrong. Blush


Dwain must have been "camping out" just waiting for you to make that mistake. I know, I know... you've both had mo-den enough of those kinds of jokes. :-P


At least no one has yet commented on how some of my solutions should be flushed down the "Drain."

Happy New Year Jeff! And to all others on this thread as well.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212357 Visits: 41977
dwain.c (1/1/2014)
Jeff Moden (1/1/2014)
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps' :-D


Doh!!! Sorry about getting your name wrong. Blush


Dwain must have been "camping out" just waiting for you to make that mistake. I know, I know... you've both had mo-den enough of those kinds of jokes. :-P


At least no one has yet commented on how some of my solutions should be flushed down the "Drain."

Happy New Year Jeff! And to all others on this thread as well.


East is East and West is West and the Dwain shall never meet. :-P

Actually, that's pretty close to accurate. We're so far apart that there's little chance of meeting in person.

Happy New Year, ol' friend.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search