Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: occurences of a character

    How can I get a count of occurences of a specific character in a string?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

    You put the statements together, and then come back for a review.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Not sure how to put all of that together.

    Quote Originally Posted by tomh53
    By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

    You put the statements together, and then come back for a review.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg plz, not a while loop

    ur doing it wrong

    Code:
    select len(str) -
           len(replace(str,'x',''))
               as number_of_xs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Thanks, that is doing it. I have never been very good at loops, and try to avoid them. Knew there has to be a quick way to do this.

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by r937
    omg plz, not a while loop

    ur doing it wrong

    Code:
    select len(str) -
           len(replace(str,'x',''))
               as number_of_xs
    naw ... not wrong ... just misguided. String character inspection was the first thing that came to mind. I obviously like your code much better than the cobbled contraption mine would be. And it would even work as a set based solution.

    Thanks rudy - this one goes into the toolkit!

    -- This is all just a Figment of my Imagination --

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

    Be aware that LEN does not count any trailing blanks, so if that possibility exists (as in this example, when there is a blank and then a couple of the target characters), you'll have to code to handle it...
    Code:
    DECLARE @str varchar(24)
    
    SET @str = 'xx xuxs ssx xx'
    
    select 	@str as OrigStr,
    	 len(@str) as lenOfOrigStr,
    	'!' + replace(@str,'x','') + '!' as XLessStr,
    	 len(replace(@str,'x','')) as LenOfXLessStr,
    	len(@str) - len(replace(@str,'x','')) as number_of_xs
    
    OrigStr         lenOfOrigStr XLessStr   LenOfXLessStr number_of_xs 
    --------------- ------------ ---------- ------------- ------------- ------------ 
    xx xuxs ssx xx  14           ! us ss !  6             8
    Mind you, I found this by accident, but I R shtill shmart.

    might I suggest something cludgy, like:
    Code:
    select len(@str) - len(replace((@str + '!'),'x','')) + 1 as number_of_xs
    Last edited by TallCowboy0614; 11-02-07 at 21:03.
    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
  •