faizi faizi - 23 days ago 5
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

Answer

The string concatenation operator in postgresql is: ||

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