es posible tener un generador de secuencia alfanumérica en sql

Necesito escribir una consulta SQL para imprimir la siguiente secuencia de aphanumberic en SQL

0001, 0002, …, 0009, 000A, …, 000Z, …, 0010, 0011, …, 001A, … y así sucesivamente hasta …, ZZZZ

tenga en count: todos los personajes son MAYÚSCULAS.

Gracias por adelantado

Podrías crear una function como esta:

create function to_base_36 (n integer) return varchar2 is q integer; r varchar2(100); begin q := n; while q >= 36 loop r := chr(mod(q,36)+case when mod(q,36) < 10 then 48 else 55 end) || r; q := floor(q/36); end loop; r := chr(mod(q,36)+case when mod(q,36) < 10 then 48 else 55 end) || r; return lpad(r,4,'0'); end; 

y luego usarlo así:

 select rownum, to_base_36(rownum) from dual connect by level < 36*36*36*36; 

O bien, sin crear una function:

 with digits as ( select n, chr(mod(n,36)+case when mod(n,36) < 10 then 48 else 55 end) d from (Select rownum-1 as n from dual connect by level < 37) ) select d1.n*36*36*36 + d2.n*36*36 + d3.n*36 + d4.n, d1.d||d2.d||d3.d||d4.d from digits d1, digits d2, digits d3, digits d4 

Puede usar esta function:

 create or replace FUNCTION SEQGEN(vinp in varchar2, iSeq in INTEGER) RETURN VARCHAR2 is vResult VARCHAR2(32); iBas INTEGER; iRem INTEGER; iQuo INTEGER; lLen CONSTANT INTEGER := 2; BEGIN iBas := length(vInp); iQuo := iSeq; WHILE iQuo > 0 LOOP iRem := iQuo mod iBas; --dbms_output.put_line('Now we divide ' || lpad(iQuo,lLen,'0') || ' by ' || lpad(iBas,lLen,'0') || ', yielding a quotient of ' || lpad( TRUNC(iQuo / iBas) ,lLen,'0') || ' and a remainder of ' || lpad(iRem,lLen,'0') || ' giving the char: ' || substr(vInp, iRem, 1)); iQuo := TRUNC(iQuo / iBas); If iRem < 1 Then iRem := iBas; iQuo := iQuo - 1; End If; vResult := substr(vInp, iRem, 1) || vResult; END LOOP; RETURN vResult; END SEQGEN; 

Prueba la function:

 SELECT * FROM ( SELECT seqgen('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',rownum + 47989 --start value ) Output, level evt FROM dual CONNECT BY level < 1679618) --stop value WHERE mod(evt,50000) = 0 OR output in ('0001','0002','0009','000A','000Z', '0010','0011','001A','ZZZZ') 

Tenga en count que si cambia la cadena, también debe cambiar el valor de inicio y detención.

Lea más acerca de los sistemas numéricos aquí: Conversión del sistema numérico – Explicación

 -- To get 00000 to ZZZZZ next auto alphanumeric sequence using this function [Please verify before use] -- This starts from 0-9 then AZ and then increase next digit from 0-9 then AZ -- You need to pass the starting/Last sequence as value to get next sequence CREATE OR REPLACE FUNCTION return_next_seq (curr_sequence VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2(4000) := NULL; retMaxval VARCHAR2(4000) := NULL; eval_digit CHAR(1) := NULL; original_sequence VARCHAR2(4000) := curr_sequence; curr1_sequence VARCHAR2(4000) := curr_sequence; BEGIN retval := original_sequence; FOR j IN REVERSE 1..LENGTH(curr1_sequence) LOOP -- Using reverse to know -- the exact digit position eval_digit := SUBSTR(curr1_sequence, LENGTH(curr1_sequence)); --IF (ASCII(eval_digit) BETWEEN 49 AND 56) OR --(ASCII(eval_digit) BETWEEN 97 AND 121) THEN IF (ASCII(eval_digit) BETWEEN 48 AND 56) OR (ASCII(eval_digit) BETWEEN 65 AND 89) THEN eval_digit := CHR(ASCII(eval_digit) +1); curr1_sequence := SUBSTR(curr1_sequence,1,LENGTH(curr1_sequence)-1); retval := curr1_sequence || eval_digit || SUBSTR(original_sequence, LENGTH(curr1_sequence || eval_digit)+1); EXIT; ELSE -- move to the next digit leaving the evaluated digit untouched. IF (ASCII(eval_digit) = 57) THEN eval_digit := CHR(ASCII(eval_digit) +8); curr1_sequence := SUBSTR(curr1_sequence,1,LENGTH(curr1_sequence)-1); retval := curr1_sequence || eval_digit || SUBSTR(original_sequence, LENGTH(curr1_sequence || eval_digit)+1); EXIT; END IF; IF (ASCII(eval_digit) = 90) THEN retMaxval := eval_digit; eval_digit := CHR(ASCII(eval_digit) -42); curr1_sequence := SUBSTR(curr1_sequence,1,LENGTH(curr1_sequence)-1); FOR k IN REVERSE 1..LENGTH(curr1_sequence) LOOP IF (ASCII(SUBSTR(curr1_sequence,LENGTH(curr1_sequence))) BETWEEN 48 AND 56) OR (ASCII(SUBSTR(curr1_sequence,LENGTH(curr1_sequence))) BETWEEN 65 AND 89) THEN retval := SUBSTR(curr1_sequence,0,LENGTH(curr1_sequence)-1) || CHR(ASCII(SUBSTR(curr1_sequence,LENGTH(curr1_sequence)))+1) || eval_digit || SUBSTR(retval, LENGTH(curr1_sequence || eval_digit)+1); ELSE IF ASCII(SUBSTR(curr1_sequence,LENGTH(curr1_sequence))) = 57 THEN retval := SUBSTR(curr1_sequence,0,LENGTH(curr1_sequence)-1) || CHR(65) || eval_digit || SUBSTR(retval, LENGTH(curr1_sequence || eval_digit)+1); ELSE IF ASCII(SUBSTR(curr1_sequence,LENGTH(curr1_sequence))) = 90 AND LENGTH(curr1_sequence)>1 THEN retval := SUBSTR(curr1_sequence,0,LENGTH(curr1_sequence)-1) || CHR(48) || eval_digit || SUBSTR(retval, LENGTH(curr1_sequence || eval_digit)+1); curr1_sequence := SUBSTR(curr1_sequence,1,LENGTH(curr1_sequence)-1); retMaxval := retMaxval||'Z'; ELSE retMaxval := retMaxval||'Z'; EXIT; END IF; END IF; END IF; END LOOP; EXIT; ELSE curr1_sequence := SUBSTR(curr1_sequence,1,LENGTH(curr1_sequence)-1); END IF; END IF; END LOOP; IF retval IS NULL OR (LENGTH(retval) = LENGTH(retMaxval)) THEN RETURN 'MAX'; END IF; RETURN retval; END; -- To verify, call this function like SELECT return_next_seq('ZY9Z') FROM dual; -- Any improvement suggestion is welcome! --- Thanks!..Sanjiv