No.of occurences of a character in a string

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Is there an inbuilt function to find the number of occurences of a character in a string. (or) Is there any other way to identify the same

Regards

Deepa.S

-- Anonymous, October 30, 2001

Answers

Hi Deepa

You can use Charindex / Patindex built-in functions within a loop to find out the no of occurences of a character in a string.

Example : declare @string varchar(20),@no_occur int,@pos int set @pos = 0

set @string = 'number of occurences' set @no_occur = 0

select @pos = charindex('o',@string,@pos+1) while @pos > 0 begin select @no_occur = @no_occur + 1 select @pos = charindex('o',@string,@pos+1) end select @no_occur

This returns 2 as the result

Sudhakar

-- Anonymous, October 31, 2001


--find n: Select occur_n=len(my_col)-len(replace(my_col,'n','')) From table_name

-- Anonymous, November 08, 2001

Moderation questions? read the FAQ