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?

  • How do you UNION with multiple CTEs?
  • Explain locking behavior in SQL Server
  • SQL server select distinct rows using values before a certain date
  • SQL Server query to find all permissions/access for all users in a database
  • SQL Insert into table variable as alias
  • Grant Select on a view not base table when base table is in a different database
  • 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.