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

This is a very interesting and useful trick. I have used it too. It really solves a problem! Thanks for bringing it up.

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

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

Brad,

    Excellent trick..........

probably another trick is replace the spaces also with empty spaces.

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