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

Question

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

Are the abbreviations in fixed size?

David דודו Markovitz
– David דודו Markovitz

2016-11-14 08:20:50 +00:00
Commented Nov 14, 2016 at 8:20 — David דודו Markovitz
– David דודו Markovitz, Commented Nov 14, 2016 at 8:20
@Dudu Markovitz No, abbreviations can be of any length

Faizal
– Faizal

2016-11-14 12:56:16 +00:00
Commented Nov 14, 2016 at 12:56 — Faizal
– Faizal, Commented Nov 14, 2016 at 12:56

Stefan · Accepted Answer · 2016-11-14 08:52:41Z

4

The string concatenation operator in postgresql is: ||

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

answered Nov 14, 2016 at 8:52

Stefan

1464 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kumar_Vikas · Accepted Answer · 2016-11-14 05:29:19Z

-1

You can try:

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

But i am not sure why do you need this type of query.

answered Nov 14, 2016 at 5:29

Kumar_Vikas

8457 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related