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?

  • Converting SQL to XML with stored procedure
  • If I place a composite index on three columns and use them in the same query but in different places, will it still be effective?
  • Time Addition in Sql Server
  • Change the column's content of a query result
  • Convert Epoch to DateTime SQL Server
  • Group by date that includes zero value but grouping on joined table
  • 5 Solutions collect form web for “Number of times a particular character appears in a string”

    There’s no direct function for this, but you can do it with a replace:

    declare @myvar varchar(20)
    set @myvar = 'Hello World'
    select len(@myvar) - len(replace(@myvar,'o',''))

    Basically this tells you how many chars were removed, and therefore how many instances of it there were.


    The above can be extended to count the occurences of a multi-char string by dividing by the length of the string being searched for. For example:

    declare @myvar varchar(max), @tocount varchar(20)
    set @myvar = 'Hello World, Hello World'
    set @tocount = 'lo'
    select (len(@myvar) - len(replace(@myvar,@tocount,''))) / LEN(@tocount)

    Look at the length of the string after replacing the sequence

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

    You can do that using replace and len.

    Count number of x characters in str:

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

    try that :

    declare @t nvarchar(max)
    set @t='aaaa'
    select len(@t)-len(replace(@t,'a',''))

    function for sql server:

    CREATE function NTSGetCinC(@Cadena nvarchar(4000), @UnChar nvarchar(100)) 
    Returns int 
     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 

    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
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.