Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: anyone have a snazzy way to count substrings?

    Hi All,

    I have a varchar that contains a comma-delimited list of integers, such as "12,34,56,78,123,1,123455".

    I need a way to count the number of numbers in the string (or, perhaps, better stated as "I need a count of substrings")

    I'm thinking there must be a number of ways to calculate the number of numbers in my list of numbers, but I can only seem to come up with looping through the string/varchar and counting the number of commas, and then adding one to that final count.

    Anybody know of a "cooler" way to do this? Everything I can think of involves stepping through a character at a time...

    any (printable) thoughts?
    THanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think it was Brett who came up with this method:


    select (len(YourString) - len(replace(YourString, ',', ''))) + 1
    Last edited by blindman; 05-03-04 at 17:53.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Outstanding!!! I thought about using "replace" but didn't come up with near as nice an idea - I guess that's why you guys get paid the big bucks!

    I know you were just free-handin' it, but the actual syntax is thus:
    select (len(@YourString) - len(replace(@YourString,',', '')) + 1)

    Exactly what I was looking for though...THANKS!!!

    You guys never let me down...someday I hope to bring a little something to the party besides questions!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I think it was Brett who came up with this method:


    select (len(YourString) - len(replace(YourString, ',', ''))) + 1

    Thanks...not hardly...I think it was Nigel who showed me....

    http://www.sqlteam.com/forums/pop_pr...isplay&id=1578

    Although if you think about it's perfectly logical....

    Once I saw that one, everytime I see a "complicated" problem, I step back and look for an easy answer...

    Doesn't always work for me...

    The theta join stuff still gives me pause...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    The theta join stuff still gives me pause...
    piece o' cake

    it's just a cross join with a loose condition

    in fact, an inner join is just a cross join with a more restrictive condition

    here's a perfect example of a theta join --

    http://www.dbforums.com/showthread.p...83#post3671683
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Hey TallCowboy

    The only way it works if you give something back. I use to be that way. Only asking question and no even trying to help someone else out there. Just remember that. Even if you take one question a week. I try to do at least one a day during the week.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Gotcha GarryDawkins,

    I am a member in quite a few forums related to the restoration of classic cars so I know the importance of contributing. Otherwise, if everyone asks the questions without answering any, ummm....well...the forums would be quite a bit more boring *LOL*

    I've got about 15 years of SQL experience, though mostly on Tandem Nonstop and Oracle, but have just recently started out with SQL Server, so am sure I can contribute once I've gotten past the initial curve.

    Meanwhile, I'll ask questions and appreciate the knowledge and willingness to help of everyone else! and the help IS appreciated!

    In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread, which is how to build a comma-delimited string for use in, for example, a "dynamic" IN... clause...(the lead-in and example are modified to my application) - I posted this in an internal forum at my company).

    I needed a way to do both, and found a way to do it easily with a function I didn't even know existed before yesterday! *L*:

    Code:
    Another, more efficient alternative is to use the COALESCE function, 
    which is much more efficient than the use of the cursor option, and also 
    shortens the cursor-supporting code block to a single select statement:
    
    DECLARE @PortfolioList varchar(100)
    
    SELECT @PortfolioList = COALESCE(@PortfolioList + ',', '') + 
       CAST(PortfolioID AS varchar(5))
    FROM Portfolio
    ORDER BY PortfolioID
    
    SELECT @PortfolioList as CSVList
    
    …which results in the following output:
    CSV_List                                                                                             
    ----------------------------------------------------------------------- 
    11,67,90,100,105,110,115,120,125,130,135,140,145,150,155
    
    (1 row(s) affected)
    
    The COALESCE function performs the magic here. When @PortfolioList is NULL 
    (the first row processed), it returns an empty string. On subsequent rows, it 
    concatenates the @PortfolioList value with a comma and the current
     PortfolioID value.
    Last edited by TallCowboy0614; 05-04-04 at 15:26.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...that just looks sooooooooooo familiar....

    Using COALESCE to Build Comma-Delimited String
    Last edited by Brett Kaiser; 05-04-04 at 14:32.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread,
    and quite interestingly so, eh? In case anyone misinterpreted my comment and is thinking about wacking me with a plagarism stick, I also reworded my "example is mine" to read, correctly, that "the lead-in and example are modified to my application"

    I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!
    Last edited by TallCowboy0614; 05-04-04 at 15:33.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TallCowboy0614
    I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!
    good developers steal

    great developers steal and pass it on to others

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    good developers steel

    great developers steel and pass it on to others


    Or they can copper, iron, nickel or gold

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Talking

    Quote Originally Posted by Brett Kaiser
    Or they can copper, iron, nickel or gold

    I once tried to make some little spheres of tin and copper, but it got too hot and mixed together somehow, and all I ended up with was a HUGE mess and a pair of bronze balls. *sigh*

    ...but I digress...

    hey, if you jack your own thread, is it still a thread-jacking?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i've had my balls bronzed, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by blindman
    Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...
    Ouch, that's gonna leave a scar...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •