Tuesday, September 06, 2005
« Back from Tech.Ed | Main | While we are geeking out with databases.... »
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]  |  Related posts:
Visual Studio 2008 Professional vs. Standard edition. Just what are the differences? Is it Features?
The var keyword (C# 3.0) - Nothing at all like VB6 Variant - It's not even a Type!
Bringing Grep back using PowerShell
iTunes messages not getting any better despite constant updates!
First DR scare at the new office
Sign your way to better quality with a x509 Certificate
 Tracked by:
"female escorts in chattanooga" (female escorts in chattanooga) [Trackback]
"sweepstakes may 2006" (sweepstakes may 2006) [Trackback]
"endodontic infections" (endodontic infections) [Trackback]
"welding hoods" (welding hoods) [Trackback]
"college dorm nudity" (college dorm nudity) [Trackback]
"jeremy paxman interview" (jeremy paxman interview) [Trackback]
"doxycycline used for" (doxycycline used for) [Trackback]
"evil deeds" (evil deeds) [Trackback]
"traffic ticket texas" (traffic ticket texas) [Trackback]
"naturist friends gallery" (naturist friends gallery) [Trackback]
"water stop rubber gloves" (water stop rubber gloves) [Trackback]
"Angina Symptoms" (Angina Symptoms) [Trackback]
"hot springs village hotel" (hot springs village hotel) [Trackback]
"us citizenship questions" (us citizenship questions) [Trackback]
"joplin missouri" (joplin missouri) [Trackback]
"lets talk dirty" (lets talk dirty) [Trackback]
"disneyland ca" (disneyland ca) [Trackback]
"garmin 72 handheld gps" (garmin 72 handheld gps) [Trackback]
"Houston act software training" (Houston act software training) [Trackback]
"tae kwon do winchester va" (tae kwon do winchester va) [Trackback]
"minor league baseball" (minor league baseball) [Trackback]
"myspace whore me button" (myspace whore me button) [Trackback]
"C2 Constellation" (C2 Constellation) [Trackback]
"buy used car" (buy used car) [Trackback]
"candida albicans alcoholism" (candida albicans alcoholism) [Trackback]
"hentai dating sim game" (hentai dating sim game) [Trackback]
"farm animal cake sprinkles" (farm animal cake sprinkles) [Trackback]
"hummer H2 SUT" (hummer H2 SUT) [Trackback]
"van norman brake lathe" (van norman brake lathe) [Trackback]
"Big Brother 2005" (Big Brother 2005) [Trackback]
"fact act" (fact act) [Trackback]
"miller generators" (miller generators) [Trackback]
"plastic card printer" (plastic card printer) [Trackback]
"sofa sleeper mattress" (sofa sleeper mattress) [Trackback]
"subwoofer auto" (subwoofer auto) [Trackback]
"Oriental Trading Post" (Oriental Trading Post) [Trackback]
"gameboy advance sp" (gameboy advance sp) [Trackback]
"import of perfume" (import of perfume) [Trackback]
"playboy playmate july 1996" (playboy playmate july 1996) [Trackback]
"vicks humidifier" (vicks humidifier) [Trackback]
"September Holidays" (September Holidays) [Trackback]
"calendar publishers" (calendar publishers) [Trackback]
"mexico pharmacies" (mexico pharmacies) [Trackback]
"audition movie spoiler" (audition movie spoiler) [Trackback]
"precut mats" (precut mats) [Trackback]
"how to construct a respirator" (how to construct a respirator) [Trackback]
"8X 4X AGP INTERFACE" (8X 4X AGP INTERFACE) [Trackback]
"Effects of Nagasaki Hiroshima Bombing" (Effects of Nagasaki Hiroshima Bombing) [Trackback]
"technical defenition of SSL" (technical defenition of SSL) [Trackback]
"zyrtec and conception" (zyrtec and conception) [Trackback]
"DEWALT ROUTER" (DEWALT ROUTER) [Trackback]
"RFID Tags" (RFID Tags) [Trackback]
"ray of light" (ray of light) [Trackback]
"purchase flagyl 500mg on line" (purchase flagyl 500mg on line) [Trackback]
"electronic components listing" (electronic components listing) [Trackback]
"eureka vacuum belts" (eureka vacuum belts) [Trackback]
"phat farm sneakers" (phat farm sneakers) [Trackback]
"computer restarts for no reason" (computer restarts for no reason) [Trackback]
"Cabinet Knobs" (Cabinet Knobs) [Trackback]
"beach babe" (beach babe) [Trackback]
"whirlpool cooktop" (whirlpool cooktop) [Trackback]
"cable descrambler" (cable descrambler) [Trackback]
"Monthly Pollen Forcast for Central Pennsylvania" (Monthly Pollen Forcast for Ce... [Trackback]
"Sentence Generator" (Sentence Generator) [Trackback]
"mars snickers sydney" (mars snickers sydney) [Trackback]
"hood insert gasket barracuda" (hood insert gasket barracuda) [Trackback]
"truth locks" (truth locks) [Trackback]
"convict leasing" (convict leasing) [Trackback]
"wire quilt hangers" (wire quilt hangers) [Trackback]
"corvette car cover" (corvette car cover) [Trackback]
"Blackhawk helicopter model" (Blackhawk helicopter model) [Trackback]
"Download Grand Theft Auto 3" (Download Grand Theft Auto 3) [Trackback]
"alaska fishing lodges" (alaska fishing lodges) [Trackback]
"sausage stuffer horns" (sausage stuffer horns) [Trackback]
"gt ollie hardshell" (gt ollie hardshell) [Trackback]
"medication cost" (medication cost) [Trackback]
"atv helmets" (atv helmets) [Trackback]
"foods high in protein" (foods high in protein) [Trackback]
"fax numbers" (fax numbers) [Trackback]
"contract cleaning company" (contract cleaning company) [Trackback]
"computer networking" (computer networking) [Trackback]
"fairpulcinogruppo" (fairpulcinogruppo) [Trackback]
"vacanzafebbraiosettimanabianca" (vacanzafebbraiosettimanabianca) [Trackback]
"upskirtitaly" (upskirtitaly) [Trackback]
"sfondipasqua" (sfondipasqua) [Trackback]
"cameraivrea" (cameraivrea) [Trackback]
"caldaiariello" (caldaiariello) [Trackback]
"top rated spyware remover" (top rated spyware remover) [Trackback]
"ministeroaffariesteri" (ministeroaffariesteri) [Trackback]
"gifanimatecavalieridellozodiaco" (gifanimatecavalieridellozodiaco) [Trackback]
"insensatoamatorialiinculate" (insensatoamatorialiinculate) [Trackback]
"clipartmatrimonio" (clipartmatrimonio) [Trackback]
"prenotazionealbergoonline" (prenotazionealbergoonline) [Trackback]
"airchina" (airchina) [Trackback]
"produzionepiscina" (produzionepiscina) [Trackback]
"good corporations" (good corporations) [Trackback]
"napoliturismo" (napoliturismo) [Trackback]
"cultmovie" (cultmovie) [Trackback]
"Lipitor And Zocor" (Lipitor And Zocor) [Trackback]
"tortadipatate" (tortadipatate) [Trackback]
"incontrisassari" (incontrisassari) [Trackback]
"giocopescapc" (giocopescapc) [Trackback]
"John J. Black Jack Pershing" (John J. Black Jack Pershing) [Trackback]
"black jack tips" (black jack tips) [Trackback]
"groom davies solicitors clacton" (groom davies solicitors clacton) [Trackback]
"dvi connector" (dvi connector) [Trackback]
"deep discount dvd" (deep discount dvd) [Trackback]
"esselen dwellings" (esselen dwellings) [Trackback]
"what is the difference between dvd-r and dvd-ram" (what is the difference betwe... [Trackback]
"entex pse" (entex pse) [Trackback]
"2005 dakine cosmo backpacks" (2005 dakine cosmo backpacks) [Trackback]
"omega psi phi pendants" (omega psi phi pendants) [Trackback]
"hideto matsumoto" (hideto matsumoto) [Trackback]
"daisy lemoine the boston terrier" (daisy lemoine the boston terrier) [Trackback]
"fukugata ichi" (fukugata ichi) [Trackback]
"dakiya dak laya mp3" (dakiya dak laya mp3) [Trackback]
"dwarf cichlids" (dwarf cichlids) [Trackback]
"henna hair dye" (henna hair dye) [Trackback]
"camcorder dv mini sony xxasdf" (camcorder dv mini sony xxasdf) [Trackback]
"dopey and grumpy dwarf halloween costume pattern" (dopey and grumpy dwarf hallo... [Trackback]
"samsung sir t351 hdtv tuner set top box w2f dvi" (samsung sir t351 hdtv tuner s... [Trackback]
"daihatsu f300s" (daihatsu f300s) [Trackback]
"dva power converter" (dva power converter) [Trackback]
"joan van patton nippon" (joan van patton nippon) [Trackback]
"pictures of cats n daises" (pictures of cats n daises) [Trackback]
"specialiste automatique en assurance dais" (specialiste automatique en assuranc... [Trackback]
"acupressure body points atlas" (acupressure body points atlas) [Trackback]
"dockers daffodil mules" (dockers daffodil mules) [Trackback]
"jan hammer crokett theme" (jan hammer crokett theme) [Trackback]
"dagger tiki kayak" (dagger tiki kayak) [Trackback]
"jan van der roost mp3" (jan van der roost mp3) [Trackback]
"psat test taking hints" (psat test taking hints) [Trackback]
"galco leather barrel guard" (galco leather barrel guard) [Trackback]
"daihatsu rocky 4x4 accesoriesrooftops" (daihatsu rocky 4x4 accesoriesrooftops) [Trackback]
"daisy clipart" (daisy clipart) [Trackback]
"tie dye t shirts" (tie dye t shirts) [Trackback]
"brick dts dvd9" (brick dts dvd9) [Trackback]
"dwl cookbooks" (dwl cookbooks) [Trackback]
"psa adhesive sanding discs" (psa adhesive sanding discs) [Trackback]
"jan sokoloff harness" (jan sokoloff harness) [Trackback]
"dwayne ganier" (dwayne ganier) [Trackback]
"neanderthal or cro-magnon dwelling" (neanderthal or cro-magnon dwelling) [Trackback]
"ludington daily news" (ludington daily news) [Trackback]
"chhabracosmetics.com" (chhabracosmetics.com) [Trackback]
"chhabramedicalgroup.com" (chhabramedicalgroup.com) [Trackback]
"arizonagreathomes.com" (arizonagreathomes.com) [Trackback]
"chhabrapharmaceuticals.com" (chhabrapharmaceuticals.com) [Trackback]
"cobetravel.com" (cobetravel.com) [Trackback]
"2podcast.net" (2podcast.net) [Trackback]
"twincircles.com" (twincircles.com) [Trackback]
"projectxena.com" (projectxena.com) [Trackback]
"syncreticorder.com" (syncreticorder.com) [Trackback]
"buy-web-site-traffic.com" (buy-web-site-traffic.com) [Trackback]
"prontostudios.com" (prontostudios.com) [Trackback]
"comeworldwide.com" (comeworldwide.com) [Trackback]
"chhabragroup.net" (chhabragroup.net) [Trackback]
"click2chico.com" (click2chico.com) [Trackback]
"twincircles.com" (twincircles.com) [Trackback]
"mpbeats.com" (mpbeats.com) [Trackback]
"chhabrahotels.com" (chhabrahotels.com) [Trackback]
"artisticwebdesigners.com" (artisticwebdesigners.com) [Trackback]
"phytolink.net" (phytolink.net) [Trackback]
"chhabrafoods.com" (chhabrafoods.com) [Trackback]
"collegepridepixels.com" (collegepridepixels.com) [Trackback]
"prontostudios.com" (prontostudios.com) [Trackback]
"chhabraracing.com" (chhabraracing.com) [Trackback]
"craftyass.com" (craftyass.com) [Trackback]
"arabtraffic.com" (arabtraffic.com) [Trackback]
"brooksgeninvest.com" (brooksgeninvest.com) [Trackback]
"diana-degarmo.net" (diana-degarmo.net) [Trackback]
"cosmeticsurgory.com" (cosmeticsurgory.com) [Trackback]
"chhabrafinancialservices.com" (chhabrafinancialservices.com) [Trackback]
"click2usa.com" (click2usa.com) [Trackback]
"bye-mail.com" (bye-mail.com) [Trackback]
"specsites.com" (specsites.com) [Trackback]
"brze.com" (brze.com) [Trackback]
"chhabraconsulting.com" (chhabraconsulting.com) [Trackback]
"internet-marketingpro.com" (internet-marketingpro.com) [Trackback]
"caramigo.com" (caramigo.com) [Trackback]
"wysiwygkennels.com" (wysiwygkennels.com) [Trackback]
"whartongcc.com" (whartongcc.com) [Trackback]
"wysiwygkennels.com" (wysiwygkennels.com) [Trackback]
"gregpayneoffenses.com" (gregpayneoffenses.com) [Trackback]
"auctionsection.com" (auctionsection.com) [Trackback]
"writewithapro.com" (writewithapro.com) [Trackback]
"writewithapro.com" (writewithapro.com) [Trackback]
"avalon-knights.com" (avalon-knights.com) [Trackback]
"wysiwygkennels.com" (wysiwygkennels.com) [Trackback]
"ladyboy6.com" (ladyboy6.com) [Trackback]
"gregpayneoffenses.com" (gregpayneoffenses.com) [Trackback]
"spaceless.net" (spaceless.net) [Trackback]
"leomoctezuma.com" (leomoctezuma.com) [Trackback]
"our-specialties.com" (our-specialties.com) [Trackback]
"writewithapro.com" (writewithapro.com) [Trackback]
"auctionsection.com" (auctionsection.com) [Trackback]
"caramigo.com" (caramigo.com) [Trackback]
"whartongcc.com" (whartongcc.com) [Trackback]
"caramigo.com" (caramigo.com) [Trackback]
"spaceless.net" (spaceless.net) [Trackback]
"hornypics.net" (hornypics.net) [Trackback]
"elite-stfu.com" (elite-stfu.com) [Trackback]
"ladyboy6.com" (ladyboy6.com) [Trackback]
"wysiwygkennels.com" (wysiwygkennels.com) [Trackback]
"our-specialties.com" (our-specialties.com) [Trackback]
"elite-stfu.com" (elite-stfu.com) [Trackback]
"caramigo.com" (caramigo.com) [Trackback]
"whartongcc.com" (whartongcc.com) [Trackback]
"caramigo.com" (caramigo.com) [Trackback]
"14themoney" (14themoney) [Trackback]
"hoodia-gardonii" (hoodia-gardonii) [Trackback]
"limogalicia" (limogalicia) [Trackback]
"bengalisource" (bengalisource) [Trackback]
"designfrederick" (designfrederick) [Trackback]
"magicalmuggleborns" (magicalmuggleborns) [Trackback]
"globalspyglass" (globalspyglass) [Trackback]
"sunmoonentertainment" (sunmoonentertainment) [Trackback]
"vedicmasseur" (vedicmasseur) [Trackback]
"bondchem" (bondchem) [Trackback]
"livecasinovenice" (livecasinovenice) [Trackback]
"hi-techventures" (hi-techventures) [Trackback]
"just-crave-some-fun" (just-crave-some-fun) [Trackback]
"annrieke" (annrieke) [Trackback]
"campus-connection" (campus-connection) [Trackback]
"narrow-way" (narrow-way) [Trackback]
"law-live" (law-live) [Trackback]
"movieminds" (movieminds) [Trackback]
"noasoftware" (noasoftware) [Trackback]
"amsteelindia" (amsteelindia) [Trackback]
"tjejen" (tjejen) [Trackback]
"alsaheronline" (alsaheronline) [Trackback]
"zyprilformen" (zyprilformen) [Trackback]
"cowboychapel" (cowboychapel) [Trackback]
"777machine" (777machine) [Trackback]
"shoppingrelated" (shoppingrelated) [Trackback]
"hoodia-gordonii-sale" (hoodia-gordonii-sale) [Trackback]
"prointercoal" (prointercoal) [Trackback]
"tyemilee" (tyemilee) [Trackback]
"moronworld" (moronworld) [Trackback]
"seedbase" (seedbase) [Trackback]
"ellanormandin" (ellanormandin) [Trackback]
"slowprocess" (slowprocess) [Trackback]
"smabackuponline" (smabackuponline) [Trackback]
"floridapolicejobs" (floridapolicejobs) [Trackback]
"autumnroad" (autumnroad) [Trackback]
"lasvegaskorea.com" (lasvegaskorea.com) [Trackback]
"curtashendel.com" (curtashendel.com) [Trackback]
"pluriamarshalljr.com" (pluriamarshalljr.com) [Trackback]
"on-your-way-to-wealth.com" (on-your-way-to-wealth.com) [Trackback]
"a1digitalphotos.com" (a1digitalphotos.com) [Trackback]
"teamx4.com" (teamx4.com) [Trackback]
"insane-boost.com" (insane-boost.com) [Trackback]
"vidustream.com" (vidustream.com) [Trackback]
"laurabozeman.com" (laurabozeman.com) [Trackback]
"eckoadmins.com" (eckoadmins.com) [Trackback]
"mdjdinc.com" (mdjdinc.com) [Trackback]
"theatomsphere.com" (theatomsphere.com) [Trackback]
"blindpigblues.com" (blindpigblues.com) [Trackback]
"scoota.com" (scoota.com) [Trackback]
"the-property-magnate.com" (the-property-magnate.com) [Trackback]
"michaelbozeman.com" (michaelbozeman.com) [Trackback]
"scr33n-raider.com" (scr33n-raider.com) [Trackback]
"pumpkinpc.net" (pumpkinpc.net) [Trackback]
"parentalsoft.com" (parentalsoft.com) [Trackback]
"greenfill.com" (greenfill.com) [Trackback]
"printstopuk.com" (printstopuk.com) [Trackback]
"codelocate.com" (codelocate.com) [Trackback]
"platinum-webhosting.com" (platinum-webhosting.com) [Trackback]
"clanmyth.com" (clanmyth.com) [Trackback]
"feelgoodindustries.net" (feelgoodindustries.net) [Trackback]
"justforuworld.com" (justforuworld.com) [Trackback]
"solowebserver.com" (solowebserver.com) [Trackback]
"pccscalifornia.com" (pccscalifornia.com) [Trackback]
"blanketonline.com" (blanketonline.com) [Trackback]
"thedvdmusic.com" (thedvdmusic.com) [Trackback]
"teddy-bears-sources.com" (teddy-bears-sources.com) [Trackback]
"ebizsuite5.com" (ebizsuite5.com) [Trackback]
"proleadsystems.com" (proleadsystems.com) [Trackback]
"fandbinc.com" (fandbinc.com) [Trackback]