Creating an alphanumeric sequence

Question

I want to create a sequence that generates values like:

A00000000
A00000001
..
..
..
A99999999
B00000000
B00000001
..
..

It should be generated with the initial alphabetic character A and once it reaches A99999999 it should changed to B and so on.

A normal sequence in Oracle won't give the alphanumeric sequence. How can I generate sequential values in this pattern?

Answer 1

You have to create 1 sequence and 1 transforming 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 ids from A00000000 to Z99999999 when called with next_id('num_seq'). The trick is the CHR(ascii_code) function, which returns the characters on positions 65-90, that is A-Z.

Edited:
Function made more general - you can pass any sequence as parameter and you can simply add/remove digits in the START WITH and MAXVALUE clause of the SEQUENCE definition, without a need of changing the function.

Answer 2

You can create a numeric sequence but convert the first two characters to a hex value on the fly. If you create your sequence as:

create sequence s42 start with 1000000000 maxvalue 1599999999;

... then have a function to simplify, though you don't strictly need it:

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 pulled off and converted to their hex equivalent, and then the rest are appended.

As a demo:

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 max value on the sequence means it will go up to F99999999, and then error if you call nextval again. I'm assuming the first digit is supposed to be hexadecimal; if you want A-Z then you can make the sequence start as 6500000000 and convert the first two digits to a character with chr() instead as 65 is converted to A, etc.

Answer 3

function generate_universal_id(pid){
    let BASE_VALUE = 100000000;
    let prefix = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    let prefix_index = pid/100000;
    let intvalue = Math.floor( prefix_index );
    prefix = prefix[intvalue];
    BASE_VALUE = BASE_VALUE + pid;
    BASE_VALUE = BASE_VALUE.toString().slice(-5);

    if(pid == 100000 || pid == 200000 || pid == 300000 || pid == 400000 || pid == 500000 || pid == 600000 || pid == 700000 || pid == 800000 || pid == 900000 ){
        BASE_VALUE = BASE_VALUE + 1;
    }
    BASE_VALUE = BASE_VALUE.toString().slice(-5)
    let universal_id = `${prefix}${BASE_VALUE}`
    console.log(universal_id);
}

generate_universal_id(100000)