[sql-server] Number of times a particular character appears in a string

Is there MS SQL Server function that counts the number of times a particular character appears in a string?

Look at the length of the string after replacing the sequence

declare @s varchar(10) = 'aabaacaa'
select len(@s) - len(replace(@s, 'a', ''))
>>6

You can do that using replace and len.

Count number of x characters in str:

len(str) - len(replace(str, 'x', ''))

BEST

DECLARE @yourSpecialMark = '/';
select len(@yourString) - len(replace(@yourString,@yourSpecialMark,''))

It will count, how many times occours the special mark '/'

Use this code, it is working perfectly. I have create a sql function that accept two parameters, the first param is the long string that we want to search into it,and it can accept string length up to 1500 character(of course you can extend it or even change it to text datatype). And the second parameter is the substring that we want to calculate the number of its occurance(its length is up to 200 character, of course you can change it to what your need). and the output is an integer, represent the number of frequency.....enjoy it.

CREATE FUNCTION [dbo].[GetSubstringCount]
(
  @InputString nvarchar(1500),
  @SubString NVARCHAR(200)
)
RETURNS int
AS
BEGIN 
        declare @K int , @StrLen int , @Count int , @SubStrLen int 
        set @SubStrLen = (select len(@SubString))
        set @Count = 0
        Set @k = 1
        set @StrLen =(select len(@InputString))
    While @K <= @StrLen
        Begin
            if ((select substring(@InputString, @K, @SubStrLen)) = @SubString)
                begin
                    if ((select CHARINDEX(@SubString ,@InputString)) > 0)
                        begin
                        set @Count = @Count +1
                        end
                end
                                Set @K=@k+1
        end
        return @Count
end

You can do it inline, but you have to be careful with spaces in the column data. Better to use datalength()

SELECT 
  ColName, 
  DATALENGTH(ColName) -
  DATALENGTH(REPLACE(Col, 'A', '')) AS NumberOfLetterA
FROM ColName;

-OR- Do the replace with 2 characters

SELECT 
  ColName, 
  -LEN(ColName)
  +LEN(REPLACE(Col, 'A', '><')) AS NumberOfLetterA
FROM ColName;

function for sql server:

CREATE function NTSGetCinC(@Cadena nvarchar(4000), @UnChar nvarchar(100)) 
Returns int 

 as  

 begin 

 declare @t1 int 

 declare @t2 int 

 declare @t3 int 

 set @t1 = len(@Cadena) 

 set @t2 = len(replace(@Cadena,@UnChar,'')) 

 set @t3 = len(@UnChar) 


 return (@t1 - @t2)  / @t3 

 end 

Code for visual basic and others:

Public Function NTSCuentaChars(Texto As String, CharAContar As String) As Long

NTSCuentaChars = (Len(Texto) - Len(Replace(Texto, CharAContar, ""))) / Len(CharAContar)

End Function

try that :

declare @t nvarchar(max)
set @t='aaaa'

select len(@t)-len(replace(@t,'a',''))

You may do this completely in-line by replacing the desired character with an empty string, calling LENGTH function and substracting from the original string's length.

SELECT 
  CustomerName, 
  LENGTH(CustomerName) -
  LENGTH(REPLACE(CustomerName, ' ', '')) AS NumberOfSpaces
FROM Customers;

Use this function begining from SQL SERVER 2016

Select Count(value) From STRING_SPLIT('AAA AAA AAA',' ');

-- Output : 3 

When This function used with count function it gives you how many character exists in string