Post reply

Finding the number of occurences of a substring within a varchar field

  • February 6, 2009 at 11:55 am

    #130608

    Hi everyone,

    I'm trying to find a way that would let me find the number of occurences of a regex based substring within a field. Specifically I need to count the number of times I see the pattern '[0-9])', (that would be a digit followed by a right paren) inside a varchar(255).

    I've not used CLR at all before and my first inclination is that I will need to write the full field out to a text file and use Perl or PowerShell to parse the string using that substring as delimiter.

    I seem to remember reading something once upon a time that would use a tally table to do something like this, but can't find a reference to that post nor think of how that would have been approached.

    Would appreciate any ideas about a SQL based solution.

    Thanks,

    Paul

  • February 6, 2009 at 12:29 pm

    #939734

    Ah-ha! Found what was I was looking for (in my bookmarks no less). Just forgot where I had seen the reference.

    For anyone else interested, here's Phil Factor's/Robyn Page's "helper" table article which is chock full of good stuff.

    http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench

  • February 8, 2009 at 12:53 pm

    #940182

    [font="Verdana"]Thanks for the link! I'll have a look at it.

    The way I would have done it (without dropping into CLR) is simply to have a loop through the string using patindex() to find the next occurrence of the pattern, and keep a running tally of the number of times I have to loop until patindex() returns 0.

    But maybe Phil has something smarter! :D[/font]

  • February 9, 2009 at 3:52 am

    #940360

    Something like this?

    declare @pattern varchar(10)

    declare @s-2 varchar(255)

    set @pattern='[0-9])'

    set @s-2 = 'ABC0)1)22)33))XYZ4)5689'

    select count(*)

    from numbers

    where substring(@s,number,len(@s)) like @pattern+'%'

    and number between 1 and len(@s)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 9, 2009 at 2:26 pm

    #940830

    [font="Verdana"]You will often see that "numbers" table referred to as a "tally" table here as well. Yeah, I need more practice around thinking about set-based results in that fashion.[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply