Custom number sequence formatting

The system I am working with has a numbering system where the numbers 0-999 are represented by the usual 0-999, but 1000 is represented by A00, followed by A01, A02, A03, etc, 1100 being B00 etc.

I can't think of a way to handle this in T-SQL without resorting to inspecting individual digits with huge case statements, and there must be a better way than that. I had thought about using Hexadecimal but that's not right.

DECLARE @startint int = 1,
        @endint int = 9999;

;WITH numbers(num)
AS
(
    SELECT @startint AS num
    UNION ALL SELECT num+1 FROM numbers
    WHERE num+1 <= @endint
)
SELECT num, convert(varbinary(8), num) FROM [numbers] N
OPTION(MAXRECURSION 0)

With this 999 is now 3E7, where it should just be 999.

This currently produces this:

Number    Sequence
0         0x00000000
1         0x00000001
...
10        0x0000000A
...
100       0x00000064
...
999       0x000003E7
1000      0x000003E8

What I'm looking for:

Number    Sequence
0         000
1         001
...
10        010
11        011
12        012
...
999       999
1000      A00
1001      A01
...
1099      A99
1100      B00
1101      B01
1200      C00

I need this to work in SQL Server 2008.

You can use integer division and modulo to separate the hundreds part from the tens. After that, you can add 64 to the quotient to get an ASCII value starting from A.

create function function dbo.fn_NumToThreeLetters(@num integer)
RETURNS nchar(3)
AS
begin
RETURN (SELECT (case 
                    when @num/1000 >0 then 
                        CHAR(( (@num-900)/100) +64) 
                        + replace(cast( @num %100 as nchar(2)),' ','0')
                    else cast(@num as nvarchar(3))
                end) 
       )
END

select dbo.fn_NumToThreeLetters(1100)
-------
B00

select dbo.fn_NumToThreeLetters(999)
-------
999

The first when clause ensures that the conversion is applied only if a number is above 1000. If it is, subtract 900 then divide by 100, so we get a number that starts from 1 for 1000, 2 for 1100, etc.

Add 64 to it to get an ASCII starting from A and convert it back to a character with CHAR.

The remainder just needs to be converted to a 2-digit nchar, where spaces are replaced with 0.

This will work only up to 3500. The question doesn't specify what should be done with larger numbers