Stored procedure with SUBSTRING in WHILE loop SQL Server 2005

Thursday, March 26, 2009 |

Below given stored procedure will count occurrence of given character in given string.


--stored procedure which will count occurence given character in @CountChar

--from @string variable

CREATE PROC CharCount(@String VARCHAR(50),@CountChar VARCHAR(1))

AS

BEGIN

DECLARE @counter INT

DECLARE @finalCount INT

SET @finalCount=0

SET @counter=LEN(@String)


WHILE @counter>=0

BEGIN

IF @CountChar=SUBSTRING(@String,@counter,1)

BEGIN

SET @finalCount=@finalCount+1

END

SET @counter=@counter-1

END

SELECT @finalCount

END

GO


--check the SP

EXEC CharCount 'ritesh shah from SQLHub.Com','h'

Happy Programming!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com


1 comments:

Anonymous said...

I think it will better code for finding occurrence of particular character from given string:

declare @String varchar(max)
declare @CountChar varchar(1)
declare @Count1 int
declare @Count2 int
declare @String2 varchar(max)
set @CountChar='h'
set @String='ritesh shah from SQLHub.Com'
set @Count1=datalength(@String)
set @String2= replace(@String,@CountChar,'')
set @Count2=datalength(@String2)

select @String 'String',@CountChar 'FindingChar'
,@Count1-@Count2 'No of Char In string'