Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Count Character in a String

Jun 15 2011 2:11AM by Robert Dennyson   

This very simple statement allows you to count how many times a certain character appears in a string.

Select Len('///xxx') - Len(Replace('///xxx', '/', ''))
Read More..   [8 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Robert Dennyson
11 · 14% · 4420
9
 
5
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

6  Comments  

  • This is a good trick,.... i used it few times before....

    commented on Jun 15 2011 2:52AM
    Ramireddy
    2 · 40% · 12972
  • This is a very interesting and useful trick. I have used it too. It really solves a problem! Thanks for bringing it up.

    commented on Jun 15 2011 4:12AM
    Jacob Sebastian
    1 · 100% · 32220
  • Good One.

    By changing this query slightly, you can even find the number of occurrences of a search/sub string inside another string.

    Read this article to know more: Find The Number of Occurrences of a Character or a Substring in a String

    Best Regards, Datta

    commented on Jun 15 2011 5:18AM
    Dattatrey Sindol (Datta)
    42 · 4% · 1336
  • Just one word of warning... LEN() will not count trailing spaces, so the following will (incorrectly) return a value of 7:

    declare @string varchar(50)
    set @string='///xxx ///'
    select len(@string)-len(replace(@string,'/',''))
    /* Returns 7 */
    

    You could use DATALENGTH() instead of LEN()...

    declare @string varchar(50)
    set @string='///xxx ///'
    select datalength(@string)-datalength(replace(@string,'/',''))
    /* Returns 6 */
    

    But that only works with VARCHAR... if the string were NVARCHAR, you'd get twice the value you want:

    declare @string nvarchar(50)
    set @string=N'///xxx ///'
    select datalength(@string)-datalength(replace(@string,N'/',N''))
    /* Returns 12 */
    

    Perhaps the best way to avoid the trailing space problem is to go back to using LEN(), but trick it by adding a non-space trailing character and adjusting the final result by 1 like so:

    declare @string varchar(50)
    set @string='///xxx ///'
    select len(@string)-len(replace(@string,'/','')+'*')+1
    /* Returns 6 */
    

    --Brad

    commented on Jun 15 2011 6:04PM
    Brad Schulz
    107 · 2% · 483
  • Brad,

        Excellent trick..........
    
    commented on Jun 15 2011 10:49PM
    Ramireddy
    2 · 40% · 12972
  • probably another trick is replace the spaces also with empty spaces.

    select len(replace(@str,' ','')) - len( replace( replace(@str,' ',''),@lookupchar,'')

    commented on Jun 15 2011 11:07PM
    Ramireddy
    2 · 40% · 12972

Your Comment


Sign Up or Login to post a comment.

"Count Character in a String" rated 5 out of 5 by 9 readers
Count Character in a String , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]