Creating Alphanumeric Sequence Oracle SQL

Question

I need to create an alphanumeric sequence with 3 Characters and 4 Numbers for my Primary Key. I've tried googling it but haven't come across anything that was clear or useful.

So, how do I create an alphanumeric sequence with 3 Characters and 4 Numbers in Oracle?

If you mean "sequence" in the strict technical sense, you can't. "Sequence" in Oracle means integers. It is also strange that you want system-generated values, but you have a very specific requirement for the format. Why do you have such a requirement? You should push back on it. Also, your requirement limits the values in the sequence to a small number of values (in the English alphabet, 26^3 * 10000). That is also a very unusual limitation on a system-generated PK. — user5683823
– user5683823, Commented Oct 12, 2016 at 19:20
Three fixed characters? If not, what would the pattern for incrementing be? e.g. 9999999 is followed by A000000, and eventually ZZZ9999? And then what? Also, which alphabet; and mixed case? — Alex Poole
– Alex Poole, Commented Oct 12, 2016 at 20:42
Please explain your requirement in more detail. Does 1 = AAA0001, 2='AAA0002, 1000 = 'AAB0000? Or something else? — APC
– APC, Commented Oct 14, 2016 at 5:31

Matthew McPeak · Accepted Answer · 2016-10-12 20:43:28Z

1

There is no out-of-the box support for such a sequence. You could use Oracle expressions to convert a numeric sequence to what you describe. E.g.,:

select a || b || c || last_4 from (
SELECT ROWNUM rn,
       lpad(MOD (ROWNUM, 10000),4,'0') last_4,
       CHR (MOD (FLOOR (ROWNUM / 10000), 26) + ASCII ('A')) c,
       CHR (MOD (FLOOR (ROWNUM / (10000 * 26)), 26) + ASCII ('A')) b,
       CHR (MOD (FLOOR (ROWNUM / (10000 * 26 * 26)), 26 * 26) + ASCII ('A')) a
FROM   DUAL
CONNECT BY ROWNUM <= POWER (26, 3) * 10000
)

answered Oct 12, 2016 at 20:43

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Christian Over a year ago

I think the author was looking for a single sequence to be generated at the time of calling. This generates all of the sequences at once, which I suppose could be stored and referenced. But there might be some blocking issues that would lead to retrieval of a duplicate sequence.

Marmite Bomber · Accepted Answer · 2016-10-12 21:31:34Z

1

The coment of @mathguy is very valid and you should rething the requirement. Anyway you may use a simple mapping transforming a number to your sequence string.

This query uses a sequence and transform it to the required format:

select 
chr(ascii('A') + mod(FLOOR(FLOOR(FLOOR(my_seq.nextval/10000)/26)/26),26))||
chr(ascii('A') + mod(FLOOR(FLOOR(my_seq.nextval/10000)/26),26)) ||
chr(ascii('A') + mod(FLOOR(my_seq.nextval/10000),26) ) ||
to_char(mod(my_seq.nextval,10000),'FM0009')  seq
from dual;

answered Oct 12, 2016 at 21:31

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

1 Comment

Tyler Christian Over a year ago

Just a note to anyone looking at this, it does require that you setup a sequence to start from ( my_seq ). And this would probably serve better if it were a function that could be called.

Collectives™ on Stack Overflow

Creating Alphanumeric Sequence Oracle SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related