Tuesday, September 06, 2005
This week's SQL Down Under with Itzik Ben-Gan

Ok, so part of this post is a plug for Greg Low's podcast, SQL Down Under -  just to get the caveats out of the way first! 

The guest in this week's show is Itzik Ben-Gan talking about the T-SQL language that is native to Microsoft SQL Server.  About 11 minutes in, his discusses how any problem you can face in T-SQL (or coding in general I'd suppose) is really a logic problem.

The example he gave (that I have illustrated below in a sample) is of the problem of counting the number of occurances of one string in another.  Instinctively, we sometimes start looking at a itterative process, to count each occurance as it occurs.

He proposes a deceptively simple solution:  Replace every occurance of the string being searched for (@needle) with a zero-length string. 

You can then compare the length of the original text  (@haystack) with the modified text (@delta) and divide that by the length of the string being searched for (@needle) to reach the number of occurances of @needle in @haystack.

Again, the logic of this solution is Itzic's not mine :)

Here's a simple implementation of this logic that illustrates the point well, and ends up a much cleaner (and possibly faster) solution than the itterative one.

---------------------------------------------------------------------------
DECLARE @needle CHAR(4)
 DECLARE @haystack CHAR(255)
 DECLARE @delta CHAR(255)

 SET @needle = 'pie'
 SET @haystack = 'meat pie, shepherds pie, pork pie, mud pie, ' +
            'cutie pie, mushroom and beef pie, meat pie, ' +
            'shepherds pie, pork pie, mud pie, cutie pie, ' +
            'mushroom and beef pie, meat pie, shepherds pie, ' +
            'pork pie, mud pie, cutie pie, mushroom and beef pie, ' +
            'are there other kinds of pie?'

SET @delta = REPLACE(@haystack, @needle, '')

 --NB: Extra brackets to make order of opperations clear.
 SELECT (len(@haystack) - len(@delta))  /  len(@needle) AS NumOccurances

---------------------------------------------------------------------------
Geeking Out!
Tuesday, September 06, 2005 3:32:58 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Saturday, September 03, 2005
Back from Tech.Ed

What a way to spend a week.  Major props to Jesper (http://blogs.technet.com/jesper%5Fjohansson/) for what could have been the best session of Tech.Ed on What no body told you about securing Microsoft SQL Server.

Saturday, September 03, 2005 12:57:56 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Tuesday, August 30, 2005
Tech.Ed 2005

Well I'm soon off to the Gold Coast for Tech.Ed



See you on Friday
Tuesday, August 30, 2005 2:11:54 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Wednesday, July 20, 2005
Ed has installed DasBlog!

Well, without having a free minute to write my own blog software I have finally gone and done it - installed someone else's.  I'll be moving all my old blog posts over to this blog... well um... sometime when I get around to it :)  -James

 

META
Wednesday, July 20, 2005 5:00:00 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [1]  |