Multiple Sequence in SQL Oracle / Varchar2 ID with letters and digits

I am trying to ‘generate’ a Varchar ID that contains letters and digits too with a sequence, but I think there will be more sequences than one.

What I am looking forward to is something like this ‘DJ_Digit_Digit_Letter_Letter_Letter’. An example would be DJ00WVX/DJ01HYZ/DJ99ZZZ. This is also my primary key so it would be good if I could not encounter any primary key errors.

I thought about working in ascii so I will generate numbers between 65 and 90 (A-Z) and Insert them into the column. Same for the numbers in ascii 45 to 57 or something like that. I don’t know how I could use more sequences on the same column like ‘letter_seq.nextvalue,digit_seq.nextvalue’, i know that this example is wrong, but it was easier for me to explain it. Maybe all my thinking is wrong

In conclusion I need to get something that starts with DJ followed by 2 digits and 3 letters, all wrapped up in a primary key. It’s ok to get like 20-30 unique entries, I think.

Thank you for your help!

Answer

To me, it looks like this:

SQL> create table test (id varchar2(10));

Table created.

SQL> create sequence seq1;

Sequence created.

SQL> insert into test (id)
  2  with
  3  a as (select chr(64 + level) la from dual
  4        connect by level <= 26),
  5  b as (select chr(64 + level) lb from dual
  6        connect by level <= 26),
  7  c as (select chr(64 + level) lc from dual
  8        connect by level <= 26)
  9  select 'DJ' || lpad(seq1.nextval, 2, '0')
 10              || la || lb || lc id
 11  from a cross join b cross join c;

17576 rows created.

SQL>

Several sample values:

SQL> with temp as
  2    (select id,
  3      row_number() Over (order by id) rna,
  4      row_Number() over (order by id desc) rnd
  5     from test
  6    )
  7  select id
  8  from temp
  9  where rna <= 5
 10     or rnd <= 5
 11  order by id;

ID
----------
DJ01AAA
DJ02AAB
DJ03AAC
DJ04AAD
DJ05AAE
DJ99OUK
DJ99OUL
DJ99OUM
DJ99OUN
DJ99OUO

10 rows selected.

SQL>

---

However, if

It’s ok to get like 20-30 unique entries, I think.

means that you need to generate at most 30 values, well, you’d easily create them manually; why would you develop any software solution for that? If you started typing, you’d be over by now.