I want final output to contain a number, which should be formatted into 8 characters. That is, 00000001 or 00003345. Output can be a string. What i am asking for is the numbers to be padded to a total of 8 characters. The default solution is to use replicate, like
select REPLICATE('0', 4) + [myNumber] AS 'the number' from table
which will return 00003345, 00003346, but also 00001. Which pads thousands to 8, but single digits to 5. We need to take in the number of digits returned before added the padding.
SELECT REPLICATE('0', 8 - DATALENGTH(myNumber)) + c1 AS 'the number',
it must be noted that the result, with the padding, is now a varchar.
We also get back a varchar if we use format
select format (myNumber,"D8")
DECLARE @d int;
set @d = 123;
If we want to sort a varchar(), we can’t, Or rather, we can but if the varchar() is a number that is sorted as a varchar() (now who would do that???), we get 1 before 2 before 2000 before 3 and so on. Sorted on the first character, not as a number. We need to cast (or convert) the number prior to sorting. Like
SELECT(cast (myNumber as int))