Postgresql : Pattern matching of values starting with "IR"

Question

If I have table contents that looks like this :

id | value
------------
1  |CT 6510
2  |IR 52
3  |IRAB
4  |IR AB
5  |IR52

I need to get only those rows with contents starting with "IR" and then a number, (the spaces ignored). It means I should get the values :

2  |IR 52
5  |IR52

because it starts with "IR" and the next non space character is an integer. unlike IRAB, that also starts with "IR" but "A" is the next character. I've only been able to query all starting with IR. But other IR's are also appearing.

select * from public.record where value ilike 'ir%'

How do I do this? Thanks.

Answer 1

You can use the operator ~, which performs a regular expression matching. e.g:

SELECT * from public.record where value ~ '^IR ?\d';

Add a asterisk to perform a case insensitive matching.

SELECT * from public.record where value ~* '^ir ?\d';

The symbols mean:

^: begin of the string

?: the character before (here a white space) is optional

\d: all digits, equivalent to [0-9]

See for more info: Regular Expression Match Operators

See also this question, very informative: difference-between-like-and-in-postgres