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

answered on Stack Overflow Oct 3, 2016 by Panagiotis Kanavos

User contributions licensed under CC BY-SA 3.0