How do you count the number of occurrences of a certain substring in a SQL varchar

Question

I have a column that has values formatted like a b c d  Is there a way to count the number of commas in that value in T-SQL

User · Accepted Answer

The first way that comes to mind is to do it indirectly by replacing the comma with an empty string and comparing the lengths  Declare  string varchar 1000  Set  string    a b c d  select len  string  - len replace  string

User · Answer

The following should do the trick for both single character and multiple character searches   CREATE FUNCTION dbo CountOccurrences       SearchString VARCHAR 1000       SearchFor    VARCHAR 1000    RETURNS TABLE AS    RETURN                SELECT COUNT    AS Occurrences              FROM                            SELECT ROW NUMBER   OVER  ORDER BY O object id  AS n                        FROM   sys objects AS O                       AS N                     JOIN                               VALUES   SearchString                             AS S  SearchString                           ON                          SUBSTRING S SearchString  N n  LEN  SearchFor      SearchFor              GO  --------------------------------------------------------------------------------------- -- Test the function for single and multiple character searches --------------------------------------------------------------------------------------- DECLARE  SearchForComma      VARCHAR 10                  SearchForCharacters VARCHAR 10     de    DECLARE  TestTable TABLE      TestData VARCHAR 30  NOT NULL     INSERT INTO  TestTable                TestData        VALUES        a b c de de  d e           abc de hijk               a b cde       SELECT TT TestData         CO Occurrences AS CommaOccurrences         CO2 Occurrences AS CharacterOccurrences FROM    TestTable AS TT        OUTER APPLY dbo CountOccurrences TT TestData   SearchForComma  AS CO        OUTER APPLY dbo CountOccurrences TT TestData   SearchForCharacters  AS CO2    The function can be simplified a bit using a table of numbers  dbo Nums       RETURN                SELECT COUNT    AS Occurrences              FROM   dbo Nums AS N                     JOIN                               VALUES   SearchString                             AS S  SearchString                           ON                          SUBSTRING S SearchString  N n  LEN  SearchFor      SearchFor

User · Answer

Quick extension of cmsjr s answer that works for strings with more than one character  CREATE FUNCTION dbo CountOccurrencesOfString        searchString nvarchar max        searchTerm nvarchar max    RETURNS INT AS BEGIN     return  LEN  searchString -LEN REPLACE  searchString  searchTerm       LEN  searchTerm  END  Usage  SELECT   FROM MyTable where dbo CountOccurrencesOfString MyColumn   MyString     1

User · Answer

Building on  Andrew s solution  you ll get much better performance using a non-procedural table-valued-function and CROSS APPLY   SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO     Usage      SELECT t  YourColumn   c StringCount     FROM YourDatabase dbo YourTable t         CROSS APPLY dbo CountOccurrencesOfString  your search string       t  YourColumn   c    CREATE FUNCTION  dbo   CountOccurrencesOfString         searchTerm nvarchar max        searchString nvarchar max     RETURNS TABLE AS     RETURN      SELECT  DATALENGTH  searchString -DATALENGTH REPLACE  searchString  searchTerm       NULLIF DATALENGTH  searchTerm   0  AS StringCount

User · Answer

I finally write this function that should cover all the possible situations  adding a char prefix and suffix to the input  this char is evaluated to be different to any of the char conteined in the search parameter  so it can t affect the result   CREATE FUNCTION  dbo   CountOccurrency     Input nvarchar max    Search nvarchar max    RETURNS int AS BEGIN     declare  SearhLength as int   len  -     Search    -   -2      declare  conteinerIndex as int   255      declare  conteiner as char 1    char  conteinerIndex       WHILE   CHARINDEX  conteiner   Search  gt 0  and   conteinerIndex gt 0       BEGIN         set  conteinerIndex    conteinerIndex-1          set  conteiner   char  conteinerIndex       END      set  Input    conteiner    Input    conteiner     RETURN  len  Input  - len replace  Input   Search           SearhLength END    usage  select dbo CountOccurrency  a b c d

User · Answer

If we know there is a limitation on LEN and space  why cant we replace the space first  Then we know there is no space to confuse LEN   len replace  string        -    - len replace replace  string        -

User · Answer

Darrel Lee I think has a pretty good answer  Replace CHARINDEX   with PATINDEX    and you can do some weak regex searching along a string  too     Like  say you use this for  pattern   set  pattern    -      char 9         Why would you maybe want to do something crazy like this   Say you re loading delimited text strings into a staging table  where the field holding the data is something like a varchar 8000  or nvarchar max       Sometimes it s easier faster to do ELT  Extract-Load-Transform  with data rather than ETL  Extract-Transform-Load   and one way to do this is to load the delimited records as-is into a staging table  especially if you may want an simpler way to see the exceptional records rather than deal with them as part of an SSIS package   but that s a holy war for a different thread

User · Answer

The Replace Len test is cute  but probably very inefficient  especially in terms of memory   A simple function with a loop will do the job   CREATE FUNCTION  dbo   fn Occurences          pattern varchar 255        expression varchar max    RETURNS int AS BEGIN      DECLARE  Result int   0       DECLARE  index BigInt   0     DECLARE  patLen int   len  pattern       SET  index   CHARINDEX  pattern   expression   index      While  index  gt  0     BEGIN         SET  Result    Result   1          SET  index   CHARINDEX  pattern   expression   index    patLen      END      RETURN  Result  END

User · Answer

You can compare the length of the string with one where the commas are removed   len value  - len replace value

User · Answer

DECLARE  records varchar 400  SELECT  records    a b c d  select  LEN  records  as  Before removing Commas    LEN  records  - LEN REPLACE  records             After Removing Commans

User · Answer

You can use the following stored procedure to fetch   values   IF  EXISTS  SELECT   FROM sys objects  WHERE object id   OBJECT ID N  dbo   sp parsedata    AND type in  N P   N PC        DROP PROCEDURE  dbo   sp parsedata  GO create procedure sp parsedata   cid integer  st varchar 1000   as   declare  coid integer   declare  c integer   declare  c1 integer   select  c1 len  st  - len replace  st              set  c 0   delete from table1 where complainid  cid    while   c lt   c1      begin       if   c lt  c1           begin           select  coid cast replace left  st CHARINDEX      st 1           as integer            select  st SUBSTRING  st CHARINDEX      st 1  1 LEN  st           end       else         begin           select  coid cast  st as integer          end       insert into table1 complainid courtid  values  cid  coid        set  c  c 1     end

User · Answer

for SQL Server 2017 declare  hits int   0  select  hits   count    from STRING SPLIT  F609 4DFA 8499       select  hits

User · Answer

The answer by  csmjr has a problem in some instances   His answer was to do this   Declare  string varchar 1000  Set  string    a b c d  select len  string  - len replace  string              This works in most scenarios  however  try running this   DECLARE  string VARCHAR 1000  SET  string    a b c d    SELECT LEN  string  - LEN REPLACE  string              For some reason  REPLACE gets rid of the final comma but ALSO the space just before it  not sure why    This results in a returned value of 5 when you d expect 4   Here is another way to do this which will work even in this special scenario   DECLARE  string VARCHAR 1000  SET  string    a b c d    SELECT LEN REPLACE  string              - LEN  string    Note that you don t need to use asterisks   Any two-character replacement will do   The idea is that you lengthen the string by one character for each instance of the character you re counting  then subtract the length of the original   It s basically the opposite method of the original answer which doesn t come with the strange trimming side-effect

User · Answer

this T-SQL code finds and prints all occurrences of pattern  p in sentence  s  you can do any processing on the sentence afterward   declare  old hit int   0 declare  hit int   0 declare  i int   0 declare  s varchar max   alibcalirezaalivisualization  declare  p varchar max   ali   while  i lt len  s    begin    set  hit charindex  p  s  i     if  hit gt  old hit      begin     set  old hit   hit     set  i  hit 1     print  hit    end   else     break  end   the result is  1 6 13 20

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

Declare  MainStr nvarchar 200  Declare  SubStr nvarchar 10  Set  MainStr    nikhildfdfdfuzxsznikhilweszxnikhil  Set  SubStr    nikhil  Select  Len  MainStr  - Len REPLACE  MainStr  SubStr       Len  SubStr

User · Answer

In SQL 2017 or higher  you can use this   declare  hits int   0 set  hits    select value from STRING SPLIT  F609 4DFA 8499         select count  hits

User · Answer

Declare  string varchar 1000   DECLARE  SearchString varchar 100   Set  string    as as df df as as as   SET  SearchString    as   select   len  string  - len replace  string   SearchString        - len  string  -          len replace  string   SearchString          2     len  SearchString

User · Answer

Perhaps you should not store data that way  It is a bad practice to ever store a comma delimited list in a field  IT is very inefficient for querying  This should be a related table

User · Answer

Accepted answer is correct    extending it to use 2 or more character in substring   Declare  string varchar 1000  Set  string    aa bb cc dd  Set  substring    aa  select  len  string  - len replace  string   substring        len  substring

[sql-server] How do you count the number of occurrences of a certain substring in a SQL varchar?

Examples related to sql-server

Examples related to tsql