SQL count rows where column value contains string

I want to count the number of rows where a certain column has a substring in the column value.

This doesn’t work

select count(column_name like '%substring%') 
from table_name 

Example: I want to count the rows including ‘no’

column_name
-----------    
noa
noit 
yot

Expected result: 2

Because ‘no’ is a substring in ‘noa’ and ‘noit’.

Answer

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.