SQL Function to Get Count of How Many Times String Appears in Column

SQL function to get count of how many times string appears in column?

An obvious but unscalable way is like this

(LENGTH(`my_column`) - LENGTH(REPLACE(`my_column`, 'my word', '')))/LENGTH('my word')

Have you investigated Full Text search in MySQL?

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

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.

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)

Count how many times a word exist in column

If I understand your case correctly, the next statement may help:

Text and table:

DECLARE @text varchar(100) = 'ABC~XYZ~123'
CREATE TABLE Data (
   Id int,
   [Text] varchar(100)
)
INSERT INTO Data
   (Id, [Text])
VALUES
   (1, 'XYZ'),
   (2, 'ABC'),
   (3, 'XYZ~ABC~AAA'),
   (4, '123~ABC')

Statement:

SELECT t.[value] AS [Word], j.[Count]
FROM STRING_SPLIT(@text, '~') t
LEFT JOIN (
   SELECT s.[value], COUNT(*) AS [Count]
   FROM Data d
   CROSS APPLY STRING_SPLIT(d.[Text], '~') s
   GROUP BY s.[value]
) j ON t.[value] = j.[value]

Result:

-----------
Word  Count
-----------
ABC   3
XYZ   2
123   1

How to count instances of character in SQL Column

In SQL Server:

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

How to count occurrences of a column value efficiently in SQL?

This should work:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

Count number of times value appears in particular column in MySQL

select email, count(*) as c FROM orders GROUP BY email

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> 

Count the number of times a string appeared in a delimited field In BIGQUERY

Consider below

select id, 
  ( select count(*)
    from unnest(split(text, ' -> ')) word
    where word = 'Res'
  ) cnt
from your_table           

if applied to sample data as in your question

output is

SQL count rows where column value contains string

Use a case expression:

select sum(case when column_name like '%substring%' then 1 else 0 end)
from table_name ;

Or, if that is the only value you want, use filtering and count(*):

select count(*)
from table_name
where column_name like '%substring%';

The first is more suitable when you have additional columns in the query.

I should note that the SQL standard has another method of accomplishing this, using filter:

select count(*) filter (where column_name like '%substring%')
from table_name ;

However, most databases do not support this syntax.

---

Related Topics