Post
Ask Question

Count Character Occurrences in a Given String in SQL Server

There are several ways  to count for the number of times a certain character occurs in a given string. Now we will see some of the ways.
 
First Method:
 
One of the ways to count for the number of times a certain character occurs in a given string is to loop through each character of the string and compare the character.
  1. CREATE FUNCTION [dbo].[ufn_CountChar] ( @InputStr VARCHAR(1000), @SearchChar CHAR(1) )  
  2. RETURNS INT  
  3. BEGIN  
  4.    DECLARE @StrLength INT  
  5.    DECLARE @index INT  
  6.    DECLARE @CharCount INT  
  7.   
  8.    SET @CharCount = 0  
  9.    SET @index = 1  
  10.    SET @StrLength = LEN(@InputStr)  
  11.   
  12.    WHILE @index <= @StrLength  
  13.    BEGIN  
  14.       IF SUBSTRING(@InputStr, @index, 1) = @SearchChar  
  15.       SET @CharCount = @CharCount + 1  
  16.       SET @index = @index + 1  
  17.    END  
  18.    RETURN @CharCount  
  19. END  
  20. GO  
Second Method:
 
Another way of determining the number of times a certain character occur in a given string without the use of a loop.
  1. CREATE FUNCTION [dbo].[ufn_CountChar] ( @InputStr VARCHAR(1000), @SearchChar CHAR(1) )  
  2. RETURNS INT  
  3. BEGIN  
  4.     RETURN (LEN(@InputStr) - LEN(REPLACE(@InputStr, @SearchChar, '')))  
  5. END  
  6. GO