Is it possible to generate an alphanumeric generator in sql

Question

I need to write a SQL query to print the next aphanumberic sequence in SQL

0001, 0002, ..., 0009, 000A, ..., 000Z, ..., 0010, 0011, ..., 001A, ... and so on to ..., ZZZZ

note: all UPPERCASE characters.

Thanks in advance

Answer 1

You can create a function like this:

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; 

and then use it like this:

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

Or, without creating a 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 

Answer 2

You can use this 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; 

Try the 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') 

Note that if you change the line, you must also change the start and stop value.

Read more about systems with numbers here: Converting System Numbers - Explanation

Answer 3

 -- 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