Create an alphanumeric sequence
I want to create a sequence that generates values like:
A00000000
A00000001
..
..
..
A99999999
B00000000
B00000001
..
..
It should be generated with a starting alphabetic character A, and once it reaches A99999999 it should be changed to B, etc.
A normal sequence in Oracle will not give an alphanumeric sequence. How can I generate consistent values in this pattern?
source to share
You need to create 1 sequence and 1 transform function:
CREATE SEQUENCE num_seq
START WITH 6500000000
INCREMENT BY 1
MAXVALUE 9099999999;
FUNCTION next_id(seq_name) RETURN VARCHAR2 IS
x VARCHAR2(28);
BEGIN
EXECUTE IMMEDIATE 'SELECT TRIM(TO_CHAR(' || seq_name || '.NextVal)) FROM dual' INTO x;
RETURN CHR(TO_NUMBER(SUBSTR(x, 1, 2))) || SUBSTR(x, 3);
END;
The function generates identifiers from A00000000
to Z99999999
when called with next_id('num_seq')
. The trick is a function CHR(ascii_code)
that returns characters at positions 65-90, i.e. AZ.
Edited:
The function is made more general - you can pass any sequence as a parameter, and you can simply add / remove digits in the START WITH and MAXVALUE clauses of the SEQUENCE definition without having to change the function.
source to share
You can create a number sequence, but convert the first two characters to a hexadecimal value on the fly. If you create your sequence like:
create sequence s42 start with 1000000000 maxvalue 1599999999;
... then you have a function to simplify, although you don't need this:
create function alpha_seq return varchar2 as
begin
return to_char(trunc(s42.nextval / 100000000), 'FMXX')
|| substr(to_char(s42.currval, 'FM0000000000'), 3);
end;
/
The sequence value is always 10 digits. The first two are stripped and converted to their hex equivalent, and then the rest are added.
As a demonstration:
select alpha_seq from dual
connect by level < 5;
ALPHA_SEQ
----------
A00000000
A00000001
A00000002
A00000003
-- skip a load of numbers
alter sequence s42 increment by 99999994;
select alpha_seq from dual;
ALPHA_SEQ
----------
A99999997
alter sequence s42 increment by 1;
select alpha_seq from dual
connect by level < 5;
ALPHA_SEQ
----------
A99999998
A99999999
B00000000
B00000001
-- skip a load of numbers
alter sequence s42 increment by 99999996;
select alpha_seq from dual;
ALPHA_SEQ
----------
B99999997
alter sequence s42 increment by 1;
select alpha_seq from dual
connect by level < 5;
ALPHA_SEQ
----------
B99999998
B99999999
C00000000
C00000001
The maximum value in the sequence means it will fall back to F99999999 and then to error if you call nextval again. I am assuming the first digit should be hexadecimal; if you want AZ then you can make the initial sequence as 6500000000 and convert the first two digits to a character using chr()
instead of 65 being converted to, etc.
source to share