How to do pattern matching in postgres regex?

467    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

Your Answer

Interviews

Parent Categories