Postgres : Select the maximum integer substring of a column value

I’m using postgresql and I’m having a trouble formulating the right sql query. The title of the question might sound weird, but it is exactly what I’m trying to achieve. I’m currently performing data curation with a particular table. If I have a table product containing :

id | designation
1  | IR15A1021
2  | IR15A1001
3  | IR15A1050
4  | AB100 
5  | AR100

What I want to do is to:

1) Get the integer substring of only and exactly those records with the designation whose pattern starts with ‘IR15A’. And by integer substring, meaning the number after the starting string ‘IR15A’. (1021,1001,1050)

2) Get the maximum integer substring so in this case :

maximum integer substring : 1050 

Because all other IR15A have max substrings less than it (1021 and 1001).

Assume correct conditions.

1) Substring after the IR15A is ALWAYS integer so no worrying about the type, it will be always integer.
2) Ignore other designation patterns. just the IR15A.

I’ve only managed to get the ‘%IR15A%’ wildcard search but I haven’t found any solution to cut the integer substring out of it and compare it with others to get the maximum. Thanks!

Source: regex

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.