Generating a Sequential Client Number

Question

I have a client based system that is needing a sequential client number in the form of the following.

First client would get A001, and then each new client through to A999. Once it hits A999, it would continue to B001-B999, and so on until Z001-Z999, when it would reset to AA001-AA999 and so on through the alphabet.

Does anyone see a way of how this could be achieved?

Answer 1

This will give you the exact numbers you asked for from A001 to ZZ999. If you want more numbers than that you will need to add logic for a third letter, etc. Note that you aren't getting 1000 numbers per letter, which makes things slightly more awkward.

WITH Numbers AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1 AS number FROM Numbers WHERE number < 701298)
SELECT 
    number,
    CASE WHEN number > 25974 THEN CHAR(64 + (number - 1) / 25974) ELSE '' END --This is the first letter (optional)
        + CHAR(65 + ((number - 1) / 999) % 26) --This is the second letter
        + FORMAT(CASE WHEN number < 1000 THEN number ELSE CASE WHEN number % 999 = 0 THEN 999 ELSE number % 999 END END, 'd3') --This is the three digit number
    AS client_id
FROM 
    Numbers 
OPTION (MAXRECURSION 0);

The Numbers CTE is just to get a suitable number of numbers (1 - 701,298). Once I have them I need to find the boundaries when the second letter changes (every 999 numbers) or the first letter changes (every 26 * 999 = 25974 numbers). Note that the first letter is suppressed until needed.

This gives you 27 * 26 * 999 client ids (the first letter can be blank or A-Z = so 27 options, the second letter can be A-Z = 26 options, the number can be 001-999 = 999 options). That's a grand total of 701,298 client ids.

I would suggest either using an IDENTITY column, or a SEQUENCE to get the "internal" id (which would be a primary key candidate), and then use a function to calculate the client id from this number. That's safer for multiple users, etc. You could use a calculated column, but that's a pretty big overhead?

Answer 2

I'd use simple integers as the key and a stored procedure (or calculated column) which translates to your desired format. I't essentially a numeric operation, check this SQL which calculates the format.

It does assume that you have no more than 2 letters in the beginning, so number of clients is under 26 * 26 * 1000.

select tmp.num as client_num, CONCAT(
CASE WHEN tmp.num < 26000 THEN '' ELSE CHAR(ASCII('A') - 1 + (tmp.num / 26000)) END,
CHAR (ASCII('A') + (tmp.num / 1000) % 26),
RIGHT('000'+CAST(tmp.num % 1000 AS VARCHAR(3)),3)) as client_id
from

(select 1 as 'num'
union
select 10
union 
select 150
union 
select 1000
union
select 25999
union 
select 26000
union 
select 27000
union 
select 100000) tmp

Returns table:

+------------+-----------+
| client_num | client_id |
+------------+-----------+
|          1 | A001      |
|         10 | A010      |
|        150 | A150      |
|       1000 | B000      |
|      25999 | Z999      |
|      26000 | AA000     |
|      27000 | AB000     |
|     100000 | CW000     |
+------------+-----------+