Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Uncommon Table Expressions


Uncommon Table Expressions

Author
Message
Peter E. Kierstead
Peter E. Kierstead
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 453
Comments posted to this topic are about the item Uncommon Table Expressions



PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2589 Visits: 1865
really liked the article...

"Keep Trying"
I cant let you do that Dave
I cant let you do that Dave
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 271
I was surprised to see the implementation of CTE recursion (in 2005 at least) performed similarly too inbuilt functions for small string lengths (read napkin test of up to 400) for the last example compared to

 SELECT @str2 = SUBSTRING(@Str1,1,@offset-1) + @char + Substring(@Str1,@offset+1,len(@str1)) 



The elapsed time was comparable

The recursive reported measurable cpu time and 2 worktable scans
The inbuilt did not report anything

Forgive if I am new to this but does that mean that inbuilt functions don't add to reported CPU ?

The inbuilt in a scalar select didn't generate a query plan on execution; I guess it wasn't considered worth caching.
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6501 Visits: 1407
Nice article Smile



Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3363 Visits: 5175
Nice Article. I would definitely like to go with this technique as there are very limited scenarios where you need huge documents to get parsed character by character. I have checked this routine for about 20,000 characters and it gave results in approx. 1 sec. Quite good.

Nice and informative...


Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


John Walker-229141
John Walker-229141
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 32
Recursive CTE's are great for answering hierarchical questions, but usages such as this strike me as a bit lame. Despite your disclaimer, it looks as though less reasoning types will use this way beyond the narrow scope intended by the article.

I'd much rather see the Database Vendor support regular expressions in a more comprehensive manner (e.g perl-compatible etc).

select replace(PhoneNumber, '\D', '', 'g') as "Cleaned Phone Number"
from .......
......
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1581 Visits: 711
Good article!
Michael.Beeby
Michael.Beeby
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 673
Those who might use 'The REPLACE_Nth_CHAR Function' might also be interested in STUFF (deletes a specified length of characters and inserts another set of characters at a specified starting point).
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16023 Visits: 9729
The "replace Nth character" doesn't actually catch the Nth instance of a particular character, it just replaces the Nth character.

Try it on "Susy sells sea shells by the sea shore.", and try using it to replace the third "s". As written, it doesn't even have an input parameter (variable) for which character to replace, nor does adding one to the case statement accomplish this.

What it really does is simply replace the third character (or whichever character you use in the case statement). Since that can be done much more easily with Stuff, I don't see a purpose for that particular piece of code. If you really want to replace the third "s" in that string, here's how I'd go about it:


declare @String varchar(1000), @String2 varchar(1000),
@Letter char(1), @Occurance int, @Replace char(1)

select @String = 'Susy sells sea shells by the sea shore.',
@String2 = '',
@Letter = 's',
@Occurance = 3,
@Replace = 'X'

;with Chars ([Char], [Pos], Ord) as
(select substring(@String, number, 1),
row_number() over (partition by substring(@String, number, 1) order by number),
number
from common.dbo.smallnumbers
where number between 1 and len(@String))
select @String2 =
@String2 +
case
when [Char] = @Letter and [Pos] = @Occurance then @Replace
else [Char]
end
from Chars
order by ord

select @String2



As far as parsing out a string character-by-character, a Numbers table can do that just as easily and just as fast, and can deal with arbitrarily large strings. That's mentioned in the article, but I have to ask: Why have two different functions for the same thing? If you're going to have one based on a Numbers table, why also have one based on a CTE?

For the phone number and other regex type functions, speed tests have been done by members of this site, and we found that a CLR regex out-performs this CTE method. Testing and discussion at:

http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

The article is okay, but there are better solutions for each of these things.

(In the Replace Nth CTE I built above, I reference my Common database and SmallNumbers table. I keep a database, called "Common" with a number of tables and functions that other databases reference. Much like a code library. The SmallNumbers table is 0 - 1,000.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Carl Federl
Carl Federl
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 4349

Forgive if I am new to this but does that mean that inbuilt functions don't add to reported CPU ?

CPU and IO for a UDF are NOT reported by setting statistics on.

The solution that I use is to save the values from the sysprocesses table for the @@spid and then after the SQL runs, calculate the difference from sysprocesses.

You will notice that there is a significant usage of CPU when a UDF is used.

SQL = Scarcely Qualifies as a Language
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