CODEWITHSUNDEEP
sqlpostgresqlpattern-matching
muffin
muffin

Reputation: 2104

Postgresql : Pattern matching of values starting with "IR"

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.

Upvotes: 2

Views: 6865

Answers (1)

Tom-db
Tom-db

Reputation: 6868

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

Upvotes: 8

Related Questions