Find Specific String or Char Count occur in a string

For getting The String count I have created three methods might be this will helpful to you.

in SQl server, we dont have any function which can return the number of words in a perticular string .but from small tricks we can do that . now Consider here we can separating the character on space basis

example :


DECLARE @String VARCHAR(4000)

SET @String = 'WelCome to D Murli Blog Spot.'

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1

Above query will return value 6 , but if the words are separate by more than one space then it will aslo count that space. but its wrong as per the answer.
in that case Create one function which can keep multiple spaces as a single space and return proper result

Below is a Function which can remove white space and all and retrun peoper result.

CREATE FUNCTION [dbo].[WordCount] ( @inStr VARCHAR(4000) )

RETURNS INT

AS

BEGIN

DECLARE @Index INT

DECLARE @Char CHAR(1)

DECLARE @PrevChar CHAR(1)

DECLARE @WordCount INT

SET @Index = 1

SET @WordCount = 0

WHILE @Index <= LEN(@InStr)

BEGIN

SET @Char = SUBSTRING(@InStr, @Index, 1)

SET @PrevChar = CASE WHEN @Index = 1 THEN ' '

ELSE SUBSTRING(@InStr, @Index - 1, 1)

END

IF @PrevChar = ' ' AND @Char != ' '

SET @WordCount = @WordCount + 1

SET @Index = @Index + 1

END

RETURN @WordCount

END

GO

This is third method that will first split the string and count the string bases on loop

DECLARE @NextString NVARCHAR(40)

DECLARE @Pos INT

DECLARE @NextPos INT

DECLARE @String NVARCHAR(40)

DECLARE @Delimiter NVARCHAR(40)

DECLARE @Counter int

Set @Counter = 0

SET @String ='18,21,22'

SET @Delimiter = ','

SET @String = @String + @Delimiter

SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)

BEGIN

SET @NextString = substring(@String,1,@Pos - 1)

--SELECT @NextString -- Show Results

SET @String = substring(@String,@pos+1,len(@String))

SET @pos = charindex(@Delimiter,@String)

Set @Counter = @Counter + 1

END