Alpha Numeric Counter Or Sequence

Here is a very simple example of how to create a basic Alpha Numeric Counter (Sequence) in Oracle.

Lets try to create Alpha Numeric Counter (Sequence) as
A000
A001
A002
.
.
A999
B000
B001
.
.
.
Z999


query to create a alpha numeric sequence may be as

SELECT
    CHR(65 + (LEVEL-1)/1000)  || TO_CHAR(MOD(LEVEL-1,1000),'000') ALNUM_CNT
FROM
    DUAL
CONNECT BY LEVEL <= 1000*26;


Related Posts:
- Setting Sequence Value to a Specific Number
- ORA-01436: CONNECT BY loop in user data
- Connect By Prior (Hierarchical Query)
- Get All Month or Week Days Names
- Oracle: Fibonacci Series by SQL
- Oracle SQL: Triangular Series (1 3 6 10 15 21)
- All about Sequences - "NEXTVAL and CURRVAL and SESSION" & "USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE"

4 comments:

  1. Hmm, it gives a space between alpa and num cars in the result

    ReplyDelete
    Replies
    1. I am aware of it, can be easily removed.

      Delete
    2. TRIM(TO_CHAR(MOD(LEVEL-1,1000),'000'))

      Delete
  2. BUT WHENEVER I PUT IT IN MY QUERY IT GOES TO INFINITE WAITING TO EXCUTE QUERY

    ReplyDelete