Display range of numbers - prefixed with an alphabet character-CodePudding

Oracle Database 19c Enterprise Edition

I have a table with various codes as listed below. Code is prefixed with an alphabet and then followed by a number. Some are in sequence, and some are individual or random independent numbers.

A1,A2,A3,A4,A5,A8,A9,A10,A11,A12,B3,B5,B7,B8,B9,B110,B111,B112,C1,C2,C3,C4,C5,C6,C7,C8

I want to display them in ranges as shown below. Here is the link to the schema and data: SQL Fiddle

Expected Output:

A1-A5  
A8-A12  
B3  
B5  
B7-B9  
B110-B112  
C1-C8

I tried solutions like http://lalitkumarb.wordpress.com/2015/07/22/find-range-of-consecutive-values-in-a-sequence-of-numbers-or-dates/ but doesn't work for me as I have the letter prefixed to the number.

CodePudding user response：

From Oracle 12, you can split the string into prefix and suffix and then use MATCH_RECOGNIZE to efficiently perform row-by-row pattern matching:

SELECT prefix || first_suffix || '-' || prefix || last_suffix AS range
FROM   (
  SELECT TRANSLATE(ref_code, 'A0123456789', 'A') AS prefix,
         TO_NUMBER(TRANSLATE(ref_code, '0ABCDEFGHIJKLMONPQRSTUVWXYZ', '0')) AS suffix
  FROM   xx_ref_codes
)
MATCH_RECOGNIZE(
  PARTITION BY prefix
  ORDER BY suffix
  MEASURES
    FIRST(suffix) AS first_suffix,
    LAST(suffix) AS last_suffix
  PATTERN (consecutive* final_row)
  DEFINE consecutive AS suffix   1 = NEXT(suffix)
);

Or, if you want to use analytic functions and then aggregate then:

SELECT prefix || MIN(suffix) || '-' || prefix || MAX(suffix) AS range
FROM   (
  SELECT prefix,
         suffix,
         suffix - ROW_NUMBER() OVER (PARTITION BY prefix ORDER BY suffix) AS grp
  FROM   (
    SELECT TRANSLATE(ref_code, 'A0123456789', 'A') AS prefix,
           TO_NUMBER(TRANSLATE(ref_code, '0ABCDEFGHIJKLMONPQRSTUVWXYZ', '0')) AS suffix
    FROM   xx_ref_codes
  )
)
GROUP BY prefix, grp

Which, for your sample data, both output:

RANGE
A1-A5
A8-A12
B3-B3
B5-B5
B7-B9
B110-B112
C1-C8

fiddle

CodePudding user response：

Could also be solved with standard SQL:

select header || min(num) || case when min(num) <> max(num) then '-' || header || max(num) end as result 
from (
    select substr(ref_code,1,1) as header, to_number(substr(ref_code,2)) as num,
        to_number(substr(ref_code,2)) - row_number() over(partition by substr(ref_code,1,1) order by to_number(substr(ref_code,2))) as grp, ref_code
    from xx_ref_codes
)
group by header, grp
;

A1-A5
A8-A12
B3
B5
B7-B9
B110-B112
C1-C8