How to get the max value of an alphanumeric column

Question

I Have a PostgreSQL database, there's a table named "labels".

Inside labels there's a column named "label" with a lot of values with the format 3 letters "GCS" and then numbers... For example: GCS1234, GCS9999, GCS14500

This should be an incremental table, where every time a new label is created, a query should take the max value and then add +1.

The former programmer's solution was to count total values that would be like GCS (disregarding the number) and to the count total add 1.

I have to make a cleanup of the database, and this won't work for me. Instead I would have to get the max value and add 1, and here is where I get the issue:

SELECT MAX(label) from labels where label ilike '%GCS%'

The result of this query is GCS9999 when the real max value at this moment is GCS14500

I found a temporal solution to get the max value by doing:

`SELECT MAX(label) from labels where label ilike '%GCS%' and lenght (label)>'7'` 

but this is just a patch, since at the moment this column values reaches something over GCS99999, the max value will be again GCS99999.

So I'm looking for a permanent solution to this, a way that I could get the max value of this alphanumeric column keeping in mind the format of the labels

Answer 1

You can use functions inside your aggregate function calls. In this case, substring (starting with the 4th character) is what you want, I believe. You also need the sort to be numeric instead of alpha, so you cast the result of the substring function to int inside the max() function.

create table simple(my_column varchar(12));

insert into simple values ('GCS1234'), ('GCS9999'), ('GCS14500');

select max(substring(my_column,4)::int)
from simple;

>> 14500

Of course you want the GCS back, so you can add that to the front.

select 'GCS'||max(substring(my_column,4)::int)
from simple;

>> GCS14500