Count the Number of Occurrences of a String in a Varchar Field

Count the number of occurrences of a string in a VARCHAR field?

This should do the trick:

SELECT 
    title,
    description,    
    ROUND (   
        (
            LENGTH(description)
            - LENGTH( REPLACE ( description, "value", "") ) 
        ) / LENGTH("value")        
    ) AS count    
FROM <table> 

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

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, ',', ''))

How to count instances of character in SQL Column

In SQL Server:

SELECT LEN(REPLACE(myColumn, 'N', '')) 
FROM ...

How to count the number of occurrences of a character in an Oracle varchar value?

Here you go:

select length('123-345-566') - length(replace('123-345-566','-',null)) 
from dual;

Technically, if the string you want to check contains only the character you want to count, the above query will return NULL; the following query will give the correct answer in all cases:

select coalesce(length('123-345-566') - length(replace('123-345-566','-',null)), length('123-345-566'), 0) 
from dual;

The final 0 in coalesce catches the case where you're counting in an empty string (i.e. NULL, because length(NULL) = NULL in ORACLE).

How to count the number of occurrences of a attribute in a string of another table

You should primarily focus on fixing your data model. Each participant to each project should be stored as a separate row rather than munged in a delimited string, possibly referencing the other table through a foreign key constraint:

project_id    participant_id
         0                 0   -- Bob
         0                 1   -- Bill
         1                 0   -- Bob
         1                 2   -- Jill
         2                 0   -- Bob

Then you could efficiently phrase the query:

select t1.*
    (select count(*) from table2 t2 where t2.participant_id = t1.id) cnt
from table1 t1

That said, given your current layout, one option uses string functions:

select t1.*, 
    (
        select count(*) 
        from table2 t2 
        where ', ' || t2.participants || ', ' like '%, ' || t1.name || ', %'
    ) cnt
from table1 t1

Count the number of occurrence of a number in string of numbers using BigQuery

Consider below approach

select raw_data, 
  ( select count(*)
    from unnest(split(raw_data)) el
    where el = '1'
  ) as appearances
from your_table         

if applied to sample data in your question - output is

Another option would be

select raw_data, 
  array_length(regexp_extract_all(raw_data, r'\b1\b')) as appearances
from your_table

with same output, obviously

MySQL Count number of substring occurrences in column

I would write this by first writing a query that just returns a list of terms we want to return. For example:

 SELECT t.terms
   FROM `table` t
  GROUP BY t.terms

Then wrap that in parens and use it as an inline view...

SELECT w.terms
  FROM ( SELECT t.terms
           FROM `table` t
          GROUP BY t.terms
       ) w
 ORDER BY w.terms

With that, we can do a join operation to look for matching rows, and get a count. Assuming a guarantee that terms doesn't contain underscore (_) or percent (%) characters, we can use a LIKE comparison.

Given that every term in our list is going to appear at least one time, we can use an inner join. In the more general case, where we might expect to return a zero count, we would use an outer join.

SELECT w.terms
     , COUNT(1) AS `COUNT`
  FROM ( SELECT t.terms
           FROM `table` t
          GROUP BY t.terms
       ) w
  JOIN `table` c
    ON c.terms LIKE CONCAT('%', w.terms ,'%')
 GROUP BY w.terms
 ORDER BY w.terms

In the LIKE comparison, the percent signs are wildcards that match any characters (zero, one or more).

If there's a possibility that terms does contain underscore or percent characters, we can escape those so they aren't considered wildcards by the LIKE comparison. An expression like this should do the trick:

 REPLACE(REPLACE( w.terms ,'_','\_'),'%','\%')

So we'd have a query like this:

SELECT w.terms
     , COUNT(1) AS `COUNT`
  FROM ( SELECT t.terms
           FROM `table` t
          GROUP BY t.terms
       ) w
  JOIN `table` c
    ON c.terms LIKE CONCAT('%',REPLACE(REPLACE( w.terms ,'_','\_'),'%','\%'),'%')
 GROUP BY w.terms
 ORDER BY w.terms

---

There are other query patterns that will return the specified result. This is just a demonstration of one approach.

---

NOTE: In the example in the question, every terms that is a substring of another terms, the substring match appears at the beginning of the terms. This query will also find matches where the term isn't at the beginning.

e.g. dartboard would be considered a match to art

The query could be modified to match terms that appear only at the beginning of other terms.

FOLLOWUP

With the example data, returns:

terms      COUNT     matched_terms
---------  --------  -------------------------
art               3  art,art deco,artistic
art deco          1  art deco
artistic          1  artistic
elephant          1  elephant
paint             3  paint,painting,paintings
painting          2  painting,paintings
paintings         1  paintings

In addition to the COUNT(1) aggregate, I also included another expression in the select list. This isn't required, but it does give some additional information about which terms were considered to be matches.

 GROUP_CONCAT(DISTINCT c.terms ORDER BY c.terms) AS `matched_terms`

NOTE: If there's a possibility that terms contains backslash characters, we can escape those characters as well, using another REPLACE

 REPLACE(REPLACE(REPLACE( w.terms ,'\\','\\\\'),'_','\_'),'%','\%')
                 ^^^^^^^^         ^^^^^^^^^^^^^

How can you find the number of occurrences of a particular character in a string using sql?

Here you go:

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count

How to count the occurrences of a string inside TEXT datatype in SQL Server 2005

Try this:

 declare @searchString varchar(max);
 set @searchString = 'something';
 declare @textTable table (txt text);
 insert into @textTable ( txt )
 values  ( 'something that has something 2 times' )

 select 
 (
  datalength(txt) - 
  datalength(replace(cast(txt as varchar(max)), @searchString, ''))
 )
 /datalength(@searchString) [Count]

   from @textTable as tt

Note that casting as varchar(max) won't truncate your text column as the max length of varchar(max) is 2^31-1 bytes or 2Gb.

---

Related Topics