Number of times a particular character appears in a string

Question

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

User · Answer

BEST  DECLARE  yourSpecialMark        select len  yourString  - len replace  yourString  yourSpecialMark        It will count  how many times occours the special mark

User · Answer

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

User · Answer

try that     declare  t nvarchar max  set  t  aaaa   select len  t -len replace  t  a

User · Answer

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

User · Answer

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  lt    StrLen         Begin             if   select substring  InputString   K   SubStrLen      SubString                  begin                     if   select CHARINDEX  SubString   InputString    gt  0                          begin                         set  Count    Count  1                         end                 end                                 Set  K  k 1         end         return  Count end

User · Answer

Look at the length of the string after replacing the sequence  declare  s varchar 10     aabaacaa  select len  s  - len replace  s   a         gt  gt 6

User · Answer

You can do that using replace and len   Count number of x characters in str   len str  - len replace str   x

User · Answer

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

User · Answer

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

User · Answer

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     gt  lt     AS NumberOfLetterA FROM ColName

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

Examples related to sql-server