Uncommon Table Expressions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Peter Kierstead » Uncommon Table Expressions

25 posts, Page 3 of 3

«««1 2 3

Uncommon Table Expressions

Rate Topic

Display Mode

Topic Options

Author

Message

Hugo Kornelis

Posted Friday, August 29, 2008 4:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 PM
Points: 5,297, Visits: 7,240

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

Post #561093

Jeff Moden

Posted Friday, August 29, 2008 6:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #561134

Hugo Kornelis

Posted Friday, August 29, 2008 7:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 PM
Points: 5,297, Visits: 7,240

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

Post #561195

Jeff Moden

Posted Friday, August 29, 2008 8:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.

--Jeff Moden

Post #561231

Hugo Kornelis

Posted Friday, August 29, 2008 8:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 PM
Points: 5,297, Visits: 7,240

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"! :D

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 :D) to that list.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #561266

« Prev Topic | Next Topic »

25 posts, Page 3 of 3

«««1 2 3

Permissions