CODEWITHSUNDEEP
sql-servert-sql
Sabilv
Sabilv

Reputation: 600

Generate Sequence Code A-Z then 0-9 on SQL Server

I have a case to generate sequence ID but alphabet first then numeric ( A-Z then 0-9 ) I see the question here and try the solution, but the solution always give me 0-9 first then A-Z

    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 
            
            ('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'),
              ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) y(number),
        (
            VALUES 
           
            ('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'),  ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) z(number)
 
)


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

the Sequence what should generated is :

AAA,...,AAZ,AA1,...,AA9,ABA,...,ABZ,AB1,..,AB9,...999

sample when I hit the A1Z the expected next value is A11, but the solution I tried give me A21 , is the behavior of SQL is start by 0-9 then A-Z , how to accomplish my requirement ?

Upvotes: 1

Views: 640

Answers (2)

Thom A
Thom A

Reputation: 95574

Perhaps this is what you want? Numbers have lower value than letters in T-SQL, so when you order them then '9' has a lower number than 'A'. What you could do, however, is check if the value is a valid int value or not, and then order the values that aren't first, and then by the character.

This gives you the following:

WITH Characters AS(
    SELECT V.C
    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')) V(C)),
Sequences AS(
    SELECT CONCAT(C1.C,C2.C,C3.C) AS Sequence,
           ROW_NUMBER() OVER (ORDER BY TRY_CONVERT(int,C1.C), C1.C, TRY_CONVERT(int,C2.C), C2.C, TRY_CONVERT(int,C3.C), C3.C) AS RN
    FROM Characters C1
         CROSS JOIN Characters C2
         CROSS JOIN Characters C3)
SELECT *
FROM Sequences;

You didn't state where 0 goes, so I assumed 'AAZ','AA0', 'AA1'...

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use ASCII function with a simple algorithm in ORDER BY, judging the char whether number or letter. if the char is number need to add 43

because the gap between Z (Capital letter) and 0 (first number) is 43, we need to add 43 to the number char to make sure the number is always behind letters.

WITH seq AS
(
      
    SELECT 
        ROW_NUMBER() OVER (ORDER BY  IIF(x.alpha LIKE '[A-Z]', ASCII(x.alpha), ASCII(x.alpha) + 43),
                                     IIF(y.number LIKE '[A-Z]', ASCII(y.number), ASCII(y.number) + 43),
                                     IIF(z.number LIKE '[A-Z]', ASCII(z.number), ASCII(z.number) + 43)) AS Id,
        CONVERT(nchar(3), x.alpha + y.number + z.number) AS Result
    FROM 
        (
            VALUES 
            ('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'),
            ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
           
        ) x(alpha) ,
        (
            VALUES 
            
            ('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'),
              ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) y(number),
        (
            VALUES 
           
            ('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'), 
            ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
        ) z(number)
 
)



SELECT *  FROM seq WHERE Id = (SELECT Id + 1 FROM seq WHERE Result = 'A1Z');

sqlfiddle

Upvotes: 1

Related Questions