The system I am working with has a numbering system where the numbers
0-999 are represented by the usual
1000 is represented by
A00, followed by
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)
999 is now
3E7, where it should just be
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
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
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
The remainder just needs to be converted to a 2-digit nchar, where spaces are replaced with
This will work only up to 3500. The question doesn't specify what should be done with larger numbers
User contributions licensed under CC BY-SA 3.0