SIGN UP MEMBER LOGIN:
   
ARTICLE

Count the Replaced Character and Occurrences Character in the string in the SQL Server

Posted by Sapna Malik Articles | SQL Server September 03, 2010
In this article, we will learn how to count the Replaced Character and Occurrences Character in the string in the SQL Server.
Reader Level:

HTML clipboard

In this article, we will learn how to count the Replaced Character and Occurrences Character in the string in the SQL Server.We will use REPLACE string function with LEN string function.I realized how collation effects the results of counting specific characters in a given especially varchar() string variables or parameters.

 

Example:-

 

USE Data

GO

DECLARE @LongSentence VARCHAR(1000)

DECLARE @FindSubString VARCHAR(100)

SET @LongSentence = 'HTML clipboard Count the Replaced Character and Occurrences Character in the string in the SQL Server'

SET @FindSubString = 'the'

SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))

CountReplacedChars,

(LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))

/LEN(@FindSubString) CountOccuranceChars

GO

Output:-

count2.bmp

User-Defined Function:- We will use User-Defined Function in this example. The User-Defined Function that returns the number of words there are in the given input string. We will count string occurrence in this function.

USE Data

GO 

CREATE FUNCTION [dbo].[WordCount] ( @InputString 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(@InputString)

BEGIN

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

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

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

                    END

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

        SET @WordCount = @WordCount + 1

    SET @Index = @Index + 1

END

RETURN @WordCount

END

GO

We will Count String Occurrence Function in this example.

USE Data

GO

DECLARE @String VARCHAR(4000)

SET @String = 'Count the Replaced Character and Occurrences Character in the string in the SQL Server' 

SELECT LEN(@String) - LEN(REPLACE(@String, 'a', '')) + 1 AS 'Count String Occurrence '

GO


Output:-

count1.bmp

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING