faizi faizi - 3 months ago 17
SQL Question

Postgresql : Count columns whose value starting with the value of another column

How do I count rows where a column value starts with a another column value ?

For example, I have table products shown below

id code abbreviation
1 AA01 AA
2 AB02 AB
3 AA03 AA
4 AA04 AB

I want to get the count of products whose code starts with abbreviation. A query like this

select count(*) from products where code ilike abbreviation+'%'

I am using postgresql 9.5.3


The string concatenation operator in postgresql is: ||

select count(*) from products where code like abbreviation || '%';