How to Have Alphanumeric Sequence Generator in SQL

Alpha-numeric sequence in SQL Server

To get the next sequence you can add a Id like

WITH seq AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY x.alpha + y.number + z.number) AS Id,
        CONVERT(nchar(3), x.alpha + y.number + z.number) AS Result
    FROM 
        (
            VALUES 
            ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'),
            ('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')
        ) x(alpha),
        (
            VALUES 
            ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) y(number),
        (
            VALUES 
            ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) z(number)
    WHERE
       NOT (NOT x.alpha BETWEEN '1' AND '9' AND y.number = '0' AND z.number = '0')
)
-- Uncomment to see all results
--SELECT * FROM seq

SELECT Result FROM seq WHERE Id = (SELECT Id + 1 FROM seq WHERE Result = 'Z01')

Result

Z02

Full count = 3573 = 999 + (26 * 99)

is it possible to have alphanumeric sequence generator in sql

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

SQL code to generate next sequence in a alphanumeric string

Assuming:

CREATE TABLE MyTable
    ([Value] varchar(4))
;

INSERT INTO MyTable
    ([Value])
VALUES
    ('16B'),
    ('23G'),
    ('128F'),
    ('128M')
;

You can do:

select top 1 
    case when SequenceChar = 'Z' then
        cast((SequenceNum + 1) as varchar) + 'A'
    else
        cast(SequenceNum as varchar) + char(ascii(SequenceChar) + 1)
    end as NextSequence
from (
    select Value, 
        cast(substring(Value, 1, CharIndex - 1) as int) as SequenceNum, 
        substring(Value, CharIndex, len(Value)) as SequenceChar
    from (
        select Value, patindex('%[A-Z]%', Value) as CharIndex
        from MyTable
    ) a
) b
order by SequenceNum desc, SequenceChar desc

SQL Fiddle Example

How to generate an alphanumeric sequence in DB2?

Here is a recursive query that iterates from 0 to 1295 and DIV & MOD functions to lookup the alpha characters:

WITH n(n,x) AS
(
SELECT 0,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT n+1,x FROM n WHERE n<1295
)
SELECT 
    SUBSTR(x, n/36 + 1,1) || SUBSTR(x, MOD(n,36) + 1,1) 
FROM n

Generate AlphaNumeric Sequence in Oracle PL/SQL

Got this:

    CREATE OR REPLACE FUNCTION GENERA_ALFANUM (N INTEGER) RETURN VARCHAR2
     IS
      Q INTEGER;
      R VARCHAR2(4);
    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;