Stored procedure with SUBSTRING in WHILE loop SQL Server 2005

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=@counter1

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

Advertisements

2 Responses to “Stored procedure with SUBSTRING in WHILE loop SQL Server 2005”

  1. Anonymous Says:

    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 intdeclare @Count2 intdeclare @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'

  2. Anonymous Says:

    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 intdeclare @Count2 intdeclare @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'


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: