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"
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"
Hmm, it gives a space between alpa and num cars in the result
ReplyDeleteI am aware of it, can be easily removed.
DeleteTRIM(TO_CHAR(MOD(LEVEL-1,1000),'000'))
DeleteBUT WHENEVER I PUT IT IN MY QUERY IT GOES TO INFINITE WAITING TO EXCUTE QUERY
ReplyDeleteWe need your team Help to create below type alpha numeric sequence using sql or plsql
ReplyDeleteSequence will be like below
A001 A002....................A999....AA01 AA02............AA99................AAAA
B001 B002.....................B999...BB01.......BB99 .........................BBBB
.
.
.
.
Z001 Z002....................Z999 ZZ01----ZZ99 ZZZ0 ZZZ1......ZZZ9 ZZZZ
any update on the above question
ReplyDelete