Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Need help to split Data Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 8:48 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 4,278, Visits: 3,717
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
Post #1524639
Posted Thursday, December 19, 2013 5:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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'



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!
Post #1524842
Posted Friday, December 20, 2013 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 13,093, Visits: 12,571
Last name is "Camps" so possessive would be Camps'


Doh!!! Sorry about getting your name wrong.


_______________________________________________________________

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 Moden's 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)
Post #1524982
Posted Wednesday, January 1, 2014 12:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps'


Doh!!! Sorry about getting your name wrong.


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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526937
Posted Wednesday, January 1, 2014 12:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526945
Posted Wednesday, January 1, 2014 5:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Jeff Moden (1/1/2014)
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps'


Doh!!! Sorry about getting your name wrong.


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.


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!
Post #1526962
Posted Thursday, January 2, 2014 1:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
dwain.c (1/1/2014)
Jeff Moden (1/1/2014)
Sean Lange (12/20/2013)
Last name is "Camps" so possessive would be Camps'


Doh!!! Sorry about getting your name wrong.


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.


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.

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526999
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse