Convert number to varchar in SQL with formatting

Question

Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting? For instance, my TINYINT has a value of 3 and I want to convert it to a VARCH of 03, so that it always shows a 2 digit number.

I don't see this ability in the CONVERT function.

Answer 1

RIGHT('00' + CONVERT(VARCHAR, MyNumber), 2)

Be warned that this will cripple numbers > 99. You might want to factor in that possibility.

Answer 2

declare @t  tinyint
set @t =3
select right(replicate('0', 2) + cast(@t as varchar),2)

Ditto: on the cripping effect for numbers > 99

If you want to cater for 1-255 then you could use

select right(replicate('0', 2) + cast(@t as varchar),3)

But this would give you 001, 010, 100 etc

Answer 3

Use the RIGHT function... e.g.

DECLARE @testnum TINYINT
SET @testnum = 3
PRINT RIGHT('00' + CONVERT(VARCHAR(2), @testnum), 2)

Answer 4

You can try this

DECLARE @Table TABLE(
     Val INT
)

INSERT INTO @Table SELECT 3
INSERT INTO @Table SELECT 30

DECLARE @NumberPrefix INT
SET @NumberPrefix = 2
SELECT  REPLICATE('0', @NumberPrefix - LEN(Val)) + CAST(Val AS VARCHAR(10))
FROM    @Table

Answer 5

What is the value range? Is it 0 through 10? If so, then try:

SELECT REPLICATE('0',2-LEN(@t)) + CAST(@t AS VARCHAR)

That handles 0 through 9 as well as 10 through 99.

Now, tinyint can go up to the value of 255. If you want to handle > 99 through 255, then try this solution:

declare @t  TINYINT
set @t =233
SELECT ISNULL(REPLICATE('0',2-LEN(@t)),'') + CAST(@t AS VARCHAR)

To understand the solution, the expression to the left of the + calculates the number of zeros to prefix to the string.

In case of the value 3, the length is 1. 2 - 1 is 1. REPLICATE Adds one zero. In case of the value 10, the length is 2. 2 - 2 is 0. REPLICATE Adds nothing. In the case of the value 100, the length is -1 which produces a NULL. However, the null value is handled and set to an empty string.

Now if you decide that because tinyint can contain up to 255 and you want your formatting as three characters, just change the 2-LEN to 3-LEN in the left expression and you're set.