Oracle: extract part of field and create next id

[tom123]

Hi guys,

I need to extract all fields that begin with "AW", there are numbers after this i.e AW11, AW12.....

Next i need to find the largert number i.e 12 in this case. So far i have

my $product_query = qq(SELECT code,
                         FROM product
                         WHERE code LIKE 'AW%');

Thanks in advance

[arnyinc]

This will chop off the first two characters and select the highest number.

select max(to_number(substr(CODE, 3, length(code)-2))) from product

However, I would recommend storing just the number and prepending "AW" to it when you need to print the number out.