How to do pattern matching in postgres regex?

334 Asked by ajithJayaraman in SQL Server , Asked on Oct 3, 2022

I had to write a simple query where I go looking for people's name that start with a B or a D :

SELECT s.name

FROM spelers s

WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'

ORDER BY 1

I was wondering if there is a way to rewrite this to become more performant. So can I avoid or and / or like?

Answered by Akansha Chawla

You could try

SELECT s.name

FROM   spelers s

WHERE  s.name SIMILAR TO '(B|D)%' 

ORDER  BY s.name

I've no idea whether or not either the above or your original expression are sargable in Postgres regex though.

If you create the suggested index would also be interested to hear how this compares with the other options.

SELECT name

FROM   spelers

WHERE  name >= 'B' AND name < 'C'

UNION ALL

SELECT name

FROM   spelers

WHERE  name >= 'D' AND name < 'E'

ORDER  BY name

How to do pattern matching in postgres regex?

Your Answer