Jeff Moden

Forum Replies Created

Viewing 15 posts - 33,991 through 34,005 (of 59,099 total)

RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

shannonjk (5/9/2011)
2) The ASCII=RIGHT(Testsstring,1)) is pulling only the ASCII character number for the rightmost character in the string, not necessarily the illegal character. What if it appears in the middle...
--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)

May 9, 2011 at 5:14 pm

#1322031
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

I'll show you how to fix the dash problem without all the special handling you've done but I still don't understand what you're trying to do with the Tally Table....
--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)

May 9, 2011 at 5:05 pm

#1322029
RE: Get the First letter of each word within a string and returned a concatenated string

peterzeke (5/9/2011)
WayneS (5/9/2011)
I used to use an XML splitter all the time... that is, until Jeff showed me the light. There is a thread on here somewhere that discusses it,...
--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)

May 9, 2011 at 10:59 am

#1321858
RE: Split string using XML

I know it's an old post but I thought I'd provide an update just in case anyone is still thinking about using XML for splitting. Please see the following...
--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)

May 9, 2011 at 10:58 am

#1321855
RE: Help with xp_DirTree

Looks like a ton-o-fun, Greg. I'll try to have a look at it tonight if someone doesn't beat me to it.
--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)

May 9, 2011 at 10:33 am

#1321822
RE: Get the First letter of each word within a string and returned a concatenated string

peterzeke (5/9/2011)
The part about "no UDF overhead" -- would putting Wayne's XML solution (combined with your recent DelimitedSplit8K function) into a udf be pointless or defeat performance?
It actually would...
--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)

May 9, 2011 at 10:24 am

#1321811
RE: Hexadecimal value to Decimal value

Ouch... that's what I was afraid of. I strongly suspect such a conversion of the hex representation of "MickeyMouse" will be pretty tough even on a BIGINT.
--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)

May 9, 2011 at 10:18 am

#1321808
RE: Add identity to existing column

If you do it in the table design mode of SSMS, all of that will be taken care of automatically and safely.
--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)

May 9, 2011 at 10:15 am

#1321807
RE: SQL Spackle - Find 0 values in sums by date

Patrick Cahill (5/9/2011)
Jeff,
I got side tracked for the last few weeks. It is almost done. I should have something to you by the end of the week.
Thanks
Patrick
Not a problem. ...
--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)

May 9, 2011 at 10:13 am

#1321804
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

Thanks, Mark. I'll have a look.
--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)

May 9, 2011 at 7:38 am

#1321727
RE: need a sp for table update

You bet. Just remember that W3Schools teaches "ANSI" SQL which is fairly rudamentary compared to T-SQL which is found in SQL Server. It IS a decent place to...
--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)

May 9, 2011 at 7:37 am

#1321726
RE: Get the First letter of each word within a string and returned a concatenated string

Thanks for the kudo, Pete.
Yes... XML is VERY effecient for concatenation of strings like the ones you have. Wayne has written a nice "SQL Spackle" article on the subject...
--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)

May 9, 2011 at 7:33 am

#1321725
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

mark hutchinson (5/9/2011)
Jeff
This article inspired me to write a TallyTable article for Access users/developers.
And thanks to the other commenters for your patience and enlightening comments.
Mark
Very cool. Hopefully,...
--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)

May 9, 2011 at 6:45 am

#1321690
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

SQLkiwi (5/9/2011)
About the little wiggly bits at the start of the green lines (the SQL CLR implementation): CLR code is compiled to an intermediate language to make it portable across...
--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)

May 9, 2011 at 6:41 am

#1321686
RE: Can you use a tally table to drive inserting X number of records in another table?

Both Paul and Nigel have good examples. I would like to point out something that Paul did in his code that I strongly recommend...
Different people built different base Tally...
--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)

May 9, 2011 at 4:46 am

#1321626

Viewing 15 posts - 33,991 through 34,005 (of 59,099 total)