extract letters and numbers from a char field

[Wabush]

Hi there,

I have a varchar2(32) field (asset_tag) that contains data like ;
PC1234
INV1234
PCM1234

The first letters indicating inventory category, and the numbers indicate the sequence.

Is there a way to show in a query the last used number of each Category? This gives the asset managers a way to see the number they should use next for their asset tag..

Thanks for any help!

 

[lewisp]

to_number(translate(asset_tag,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789'))

 

[jee]

What about this:

SELECT RTRIM (asset_tag, '0123456789'), 
       MAX(TO_NUMBER(LTRIM(
       asset_tag,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')))
FROM test
GROUP BY RTRIM (asset_tag, '0123456789');
[\code]