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.

You should use regex.

President Camacho
– President Camacho

2015-06-10 06:00:41 +00:00
Commented Jun 10, 2015 at 6:00 — President Camacho
– President Camacho, Commented Jun 10, 2015 at 6:00

Tom-db · Accepted Answer · 2020-12-17 09:23:46Z

8

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

edited Dec 17, 2020 at 9:23

answered Jun 10, 2015 at 6:25

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

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

1 Comment

muffin Over a year ago

thank you for the explanation, gave me a jumpstart how to do regex.

Collectives™ on Stack Overflow

Postgresql : Pattern matching of values starting with "IR"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related