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:-
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:-
