How to Count Instances of Character in SQL Column

How to count instances of character in SQL Column

In SQL Server:

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

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)

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

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 occurrences of character in a string and Group By with SQL

Try this, even I still don't know what you exactly want to do till now, but try it.

SELECT
    book,
    chapter,
    group_concat(text) as text,    
    ROUND (   
        (
            LENGTH(group_concat(text))
            - LENGTH( REPLACE ( group_concat(text), "hello", "") ) 
        ) / LENGTH("hello")        
    ) AS `count`
FROM my_table
GROUP BY chapter, book

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

---

Related Topics