Number of occurences of character in a string 1

[deeparajesh]

Is there an inbuilt function to find the number of occurences of character in a string. Or is there any other way in which you can accomplish the same

 

[tlbroadbent]

I'm not aware of a built in function. Here is a stored procedure that will count occurrences of one string in another string.

-- Count String Occurrences
-- SQL Procedure created by Terry Broadbent
-- Oct 30 2001
--
-- This procedure has been tested in SQL 2000
-- It should work in SQL 7 and 6.5
--
-- Execute procedure
-- 2 required input variables, 1 output variable
-- exec sp_CountStringOccurrences
-- [@str =] 'character string',
-- [@Find =] 'a',
-- [@Cnt =] @retcnt output
--
Create proc sp_CountStringOccurrences
@str varchar(8000), -- the string to string to search
@find varchar (20), -- the string to count
@cnt int output -- the return value
As

Declare @pos int, @tmp int

Set @pos=1
Set @cnt=0

While @pos<=len(@str)
Begin
Set @tmp=charindex(@find, @str, @pos)
If @tmp = 0 Break
Else
Begin
Set @cnt=@cnt+1
Set @pos=@tmp+1
End
End

Example of execution: returns 7

Declare @cnt int
Exec sp_CountStringOccurrences
'Tek-Tips is the best technical site on the Web!',
'e',
@cnt output

Print @cnt

If you run SQL 2000, I have a UDF that performs this function. Let me know if you want the UDF. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[deeparajesh]

Terry

Thank you.

I could acheive this in a more simpler way :

Declare @string varchar(1000)
Declare @search char(1)

set @search='r'
set @string='arghjikrkrtruiorkrmrnrjrtyuir'

select (len(@string)-len(replace(@string,@search,''))) as NumberOccured

 

[tlbroadbent]

Very good! That's a great solution. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.