Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert number to varchar in SQL with formatting

Tags:

sql

tsql

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.

like image 550
Jeff Stock Avatar asked Nov 19 '09 16:11

Jeff Stock


People also ask

How do I change a number to a varchar?

TO_CHAR (number) converts n to a value of VARCHAR2 datatype, using the optional number format fmt . The value n can be of type NUMBER , BINARY_FLOAT , or BINARY_DOUBLE . If you omit fmt , then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

Can we convert int to varchar in SQL?

Converting int to string/varchar using Cast() So, in the above example, we have declared a variable of integer data type and assigned a value to the variable. After this, we are using the Cast() function to convert the variable to the varchar data type of length 10.


2 Answers

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

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

like image 79
Tomalak Avatar answered Oct 10 '22 06:10

Tomalak


Use the RIGHT function... e.g.

DECLARE @testnum TINYINT
SET @testnum = 3
PRINT RIGHT('00' + CONVERT(VARCHAR(2), @testnum), 2)
like image 43
kevchadders Avatar answered Oct 10 '22 05:10

kevchadders