replicate and sort

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")

or

DECLARE @d int;
set @d = 123;
select format(@d,'D8')

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 myNumber FROM table ORDER BY CAST(myNumber as INT)

or

SELECT (cast (myNumber as int)) FROM table ORDER BY myNumber
Advertisements