I am looking for a oracle sql solution for the below problem:

CodePudding user response：

Here's one option (as union of 3 different cases).

Sample data:

SQL> with test (col) as
  2    (select 'A-D'      from dual union all
  3     select 'J-K'      from dual union all
  4     select 'X-20'     from dual union all
  5     select 'XX-20542' from dual union all
  6     select 'A/B'      from dual union all
  7     select 'J/K'      from dual
  8    )

Query begins here:

  9  -- A-D option:
 10  select col,
 11         chr(ascii(substr(col, 1, 1))   column_value - 1) res
 12  from test cross join
 13    table(cast(multiset(select level from dual
 14                        connect by level <= ascii(substr(col, -1)) - ascii(substr(col, 1, 1))   1
 15                       ) as sys.odcinumberlist))
 16  where regexp_like(col, '[[:alpha:]]-[[:alpha:]]')
 17  -- XX-20542 option:
 18  union all
 19  select col, 'No action / ignore'
 20  from test
 21  where regexp_like(col, '[[:alpha:]] -\d ')
 22  -- A/B option:
 23  union all
 24  select col,
 25         regexp_substr(col, '[[:alpha:]]', 1, column_value)
 26  from test cross join
 27    table(cast(multiset(select level from dual
 28                        connect by level <= ascii(substr(col, -1)) - ascii(substr(col, 1, 1))   1
 29                       ) as sys.odcinumberlist))
 30  where regexp_like(col, '[[:alpha:]]/[[:alpha:]]');

Result:

COL      RES
-------- ------------------
A-D      A
A-D      B
A-D      C
A-D      D
J-K      J
J-K      K
X-20     No action / ignore
XX-20542 No action / ignore
A/B      A
A/B      B
J/K      J
J/K      K

12 rows selected.

SQL>

CodePudding user response：

You can use a single recursive query:

WITH ranges (value, min_value, max_value) AS (
  SELECT value,
         REGEXP_SUBSTR(value, '^([A-Z])[[:punct:]]([A-Z])$', 1, 1, 'i', 1),
         REGEXP_SUBSTR(value, '^([A-Z])[[:punct:]]([A-Z])$', 1, 1, 'i', 2)
  FROM   table_name
UNION ALL
  SELECT value,
         CHR(ASCII(min_value)   1),
         max_value
  FROM   ranges
  WHERE  min_value < max_value
)
SEARCH DEPTH FIRST BY value SET rn
SELECT value,
       COALESCE(min_value, 'ignore') AS result
FROM   ranges

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'A-D'  FROM DUAL UNION ALL
SELECT 'J-K'  FROM DUAL UNION ALL
SELECT 'XX-Y' FROM DUAL UNION ALL
SELECT 'Y-ZZ' FROM DUAL UNION ALL
SELECT 'B/C'  FROM DUAL UNION ALL
SELECT 'E/E'  FROM DUAL;

Outputs:

VALUE	RESULT
A-D	A
A-D	B
A-D	C
A-D	D
B/C	B
B/C	C
E/E	E
J-K	J
J-K	K
XX-Y	ignore
Y-ZZ	ignore

db<>fiddle here