parse string

Question

parse string

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

Sean Lange (5/16/2012)
OMG :w00t:
Jeff Moden posted a loop.
There is only darkness now, there is no sunshine. The mayans were correct.
Parden me, I must send out an email blast to our IT department 😀
Please don't step on my dentures.
+1 - ROFL

my thoughts exactly. i figured jeff would have some elaborate tally table solution that was faster than the first tally table solution. as least i now know there are times when a loop is faster but the cases are very slim.

now where are my dentures??:hehe::hehe:

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

capn.hector (5/16/2012)
Sean Lange (5/16/2012)
OMG :w00t:
Jeff Moden posted a loop.
There is only darkness now, there is no sunshine. The mayans were correct.
Parden me, I must send out an email blast to our IT department 😀
Please don't step on my dentures.
+1 - ROFL
my thoughts exactly. i figured jeff would have some elaborate tally table solution that was faster than the first tally table solution. as least i now know there are times when a loop is faster but the cases are very slim.
now where are my dentures??:hehe::hehe:

It's that 0.1% of the 99.9% that I normally don't talk about. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Jeff Moden (5/16/2012)
capn.hector (5/16/2012)
Sean Lange (5/16/2012)
OMG :w00t:
Jeff Moden posted a loop.
There is only darkness now, there is no sunshine. The mayans were correct.
Parden me, I must send out an email blast to our IT department 😀
Please don't step on my dentures.
+1 - ROFL
my thoughts exactly. i figured jeff would have some elaborate tally table solution that was faster than the first tally table solution. as least i now know there are times when a loop is faster but the cases are very slim.
now where are my dentures??:hehe::hehe:
It's that 0.1% of the 99.9% that I normally don't talk about. 😉

So we are the 0.1%???

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Whoa! Did anyone else feel that?!? I felt a great .. disturbance in the Force ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff:

Do you have a test-set that I can use to test against?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

RBarryYoung (5/16/2012)
Jeff:
Do you have a test-set that I can use to test against?

Yep... I'll post it soon. Gotta clean it up a bit.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

You know me... I don't go into these things blind. I always have test data. Helps me keep my alligator mouth from overloading my humming bird hinny. 😛

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('Tempdb..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create and populate the test table with test data on the fly.

-- Takes about 00:02:16 to run on my old single CPU box.

-- (Heh... try THIS with an rCTE or While Loop. ;-) )

SELECT TOP 1000000

EncodedString =

CAST(

(

SELECT TOP (ABS(CHECKSUM(NEWID()))%10+5)

CASE

WHEN ABS(CHECKSUM(NEWID()))%10 = 0 THEN ']['

WHEN ABS(CHECKSUM(NEWID()))%10 BETWEEN 1 AND 2 THEN '['

ELSE ']'

END

+ CHAR(ABS(CHECKSUM(NEWID()))%26 + 97)

FROM sys.all_columns ac3

WHERE ac1.Object_ID <> ac2.Object_ID --I'll bet you wonder why these are here ;-)

OR ac1.Object_ID = ac2.Object_ID

FOR XML PATH ('')

)

AS VARCHAR(50))

INTO #MyHead

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jeff Moden (5/16/2012)
You know me... I don't go into these things blind. I always have test data. Helps me keep my alligator mouth from overloading my humming bird hinny. 😛

Yep, that's why I asked. 🙂 Though that description does raise some disturbing genetic questions ... 😀

Thanks..

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

RBarryYoung (5/17/2012)
Though that description does raise some disturbing genetic questions ... 😀

BWAAA-HAAAA!!!!... dunno 'bout all that... have you taken a good look at your most recent avator? 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff Moden (5/17/2012)
RBarryYoung (5/17/2012)
Though that description does raise some disturbing genetic questions ... 😀
BWAAA-HAAAA!!!!... dunno 'bout all that... have you taken a good look at your most recent avator? 😛

Ahh, the miracle of over-medication... :sick:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

Jeff Moden (5/16/2012)
You know me... I don't go into these things blind. I always have test data. Helps me keep my alligator mouth from overloading my humming bird hinny. 😛
...

I take it for granted (from you), that the test between scalar udf (with loop) and table valued one did show that the scalar one is more appropriate in this case. Have you tried CLR function?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Eugene Elutin (5/18/2012)
Jeff Moden (5/16/2012)
You know me... I don't go into these things blind. I always have test data. Helps me keep my alligator mouth from overloading my humming bird hinny. 😛
...
I take it for granted (from you), that the test between scalar udf (with loop) and table valued one did show that the scalar one is more appropriate in this case. Have you tried CLR function?

To be absolutely honest, I've not tried an iTVF for this particular one because it's nearly identical to another that I did test. The other one was an "Initial Caps" function that beat the Tally Table/CTE/STUFF and Tally CTE Split/XML Reassemble methods quite handily.

What I didn't want to do was to post either of those solutions too early because it might change someones approach on it all. Someone might be able to come up with a wiz-bang iTVF on it using an approach that none of us have considered if I've not contaminated their thought process. It's like what Dwaine C. did with the "n-tuple" problem... he came up with a way with an rCTE that I wouldn't have considered. I don't believe it's the fastest method (not done testing yet) but it's different and has potential.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

Jeff Moden (5/18/2012)

It's like what Dwaine C. did with the "n-tuple" problem... he came up with a way with an rCTE that I wouldn't have considered. I don't believe it's the fastest method (not done testing yet) but it's different and has potential.

Thanks for the compliment Jeff. Coming from you it really means something to me.

[shameless-plug]

For those of you that may have missed it, the article Jeff refers to is here: http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/

[/shameless-plug]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St