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'.
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.