How to create alphanumeric Sequence without using Trigger

Question

Sequence Should looks like :-

A1
A2
A3
B1
B2
B3
C1
C2
C3

You do understand that your sequence will almost inevitably have gaps? either through deletion, uncommitted inserts, etc? And, as commented, what do you do if the table grow past 78 rows? If it is a fixed set of data and you are just generating it once - then it's not hard. Lalit gives a good example of how. But if the data is variable, then you may need to think this through a bit more — Michael Broughton, Oct 20 '15 at 16:36
@RanjanJena If the answer has helped you then mark it as answered. — Lalit Kumar B, Oct 20 '15 at 18:45

score 2 · Answer 1 · answered Oct 20 '15 at 16:07

You could do it using ROW GENERATOR technique.

SQL>  WITH data
  2       AS (SELECT Chr(65 + LEVEL - 1) str
  3           FROM   dual
  4           CONNECT BY LEVEL <= 3)
  5  SELECT str
  6         ||column_value AS alphanumeric_sequence
  7  FROM   data,
  8         TABLE(Cast(MULTISET (SELECT LEVEL
  9         FROM   dual
 10         CONNECT BY LEVEL < 3 + 1) AS sys.ODCINUMBERLIST));

ALPHANUMERIC_SEQUENCE
-----------------------------------------
A1
A2
A3
B1
B2
B3
C1
C2
C3

9 rows selected.

The LEVEL is hard-coded as 3 above, you could change it as per your requirement. The example is as per your posted output.

How to create alphanumeric Sequence without using Trigger

1 Answers1