CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Parthiban D
Parthiban D

Reputation: 23

Issue when getting max value of an alphanumeric sequence in oracle

I have an alphanumerical sequence from A0000 to ZZZ99 created in the following manner. 

A0000 until Z9999. Then
AA000 until AZ999. Then 
BA000 until ZZ999. Then
AAA00 until ZZZ99.

I want to get the max() value of the sequence but it is not coming in the same manner as it is created.

SELECT MAX(VAL) MAX_VAL FROM (select 'A0001' as val from dual union all
select 'A0087' as val from dual union all
select  'ABA00' as val from dual union all
select  'AAZ00' as val from dual union all
select 'B0032' as val from dual );

This sample query returns B0032 whereas i need ABA00 to be returned.

Maybe converting all characters as ASCII and then taking the max will help me solve the issue but i don't know how to change each character to ASCII dynamically in a query.

Any help will be appreciated. Thanks

Upvotes: 1

Views: 68

Answers (1)

Radagast81
Radagast81

Reputation: 3006

You have to order your data first based on the first accurance of a digit and then get the max. In ORACLEyou can do that by the following query:

WITH dat AS (select 'A0001' as val from dual union all
             select 'A0087' as val from dual union all
             select 'ABA00' as val from dual union all
             select 'AAZ00' as val from dual union all
             select 'B0032' as val from dual)
SELECT MAX(val) KEEP (dense_rank last ORDER by REGEXP_INSTR(val, '\d')) FROM dat

Reason is that in lexigraphical order every word starting with an A is lower then any word starting with a B but in your case the first criterium is on which position occurs the first digit:

B0032 -> Position 2 and therefore B0032 < ABA00 which has Position 4.

Upvotes: 1

Related Questions