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


Uncommon Table Expressions


Uncommon Table Expressions

Author
Message
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10819 Visits: 11967
The article presents a nice example of a possible use for a new technique - but unfortunately, the choice of examples is a bit bad.

I think every advanced SQL Server developer should make sure to know what's in his/her toolkit. And that involves a thorough knowledge of all builtin functions. All too often, that knowledge lacks, and people invent wheels that are already built into the product.

Example 1 ("occurs") to count the number of characters 't' in the string:
SELECT LEN(@Str) - LEN(REPLACE(@Str, 't', '')
Added advantage - can also cound substrings of longer length:
SELECT (LEN(@Str) - LEN(REPLACE(@Str, 'test', '')) / LEN('test')

Example 2: ("phone_clean") to remove non-numeric characters:
At first glance a good example. Though I agree that using a CLR function to leverage regex functionality is better (not primarly for speed, but because regex functionality is existing, tried-and-tested). Unless you have a DBA who forbids CLR functions, of course.
On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.

Example 3: ("replace_Nth_char") to replace a specific character in the middle of the string:
SELECT STUFF(@Str1,3,1,'0')
Added advantages: (1) avoids undocumented string concatenation (see above), and (2) both replaced and replacement string can be of any length
SELECT STUFF(@Str1, 12, 20, '')
SELECT STUFF(@Str1, 10, 0, 'Inserted text')


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84841 Visits: 41069
Hugo Kornelis (8/29/2008)
On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.


Heh... even using something that is documented is no guarantee that it won't change or get deprecated and go away.

--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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10819 Visits: 11967
Jeff Moden (8/29/2008)
Hugo Kornelis (8/29/2008)
On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.


Heh... even using something that is documented is no guarantee that it won't change or get deprecated and go away.


Hi Jeff,

True - but if it's documented, it'll be marked as deprecated for at least one major version before beinig really removed from the product (well, unless it's Notification Services, or English Query, or ...). If it's undocumented, it might change tomorrow, and you wouldn't know about it until you got an emergeny call from your manager... Sad


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84841 Visits: 41069
Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.

--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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10819 Visits: 11967
Jeff Moden (8/29/2008)
Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.


Hi Jeff,

Well, I *did* write "usually"! BigGrin

Of drat, I now see I didn't w00t. But I did at least indicate that there have been exceptions. You can add sp_makewebtask (that I never even knew existed until I read this message BigGrin) to that list.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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