CODEWITHSUNDEEP
sqlt-sql
Jeff Stock
Jeff Stock

Reputation: 3856

Convert number to varchar in SQL with formatting

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.

Upvotes: 20

Views: 115557

Answers (8)

happycamper
happycamper

Reputation: 1

Had the same problem with a zipcode field. Some folks sent me an excel file with zips, but they were formatted as #'s. Had to convert them to strings as well as prepend leading 0's to them if they were < 5 len ...

declare @int tinyint
set     @int = 25
declare @len tinyint
set     @len = 3

select right(replicate('0', @len) + cast(@int as varchar(255)), @len)

You just alter the @len to get what you want. As formatted, you'll get...

001
002
...
010
011
...
255

Ideally you'd "varchar(@len)", too, but that blows up the SQL compile. Have to toss an actual # into it instead of a var.

Upvotes: 0

Niren Pillay
Niren Pillay

Reputation: 11

Here's an alternative following the last answer

declare @t tinyint,@v tinyint
set @t=23
set @v=232
Select replace(str(@t,4),' ','0'),replace(str(@t,5),' ','0')

This will work on any number and by varying the length of the str() function you can stipulate how many leading zeros you require. Provided of course that your string length is always >= maximum number of digits your number type can hold.

Upvotes: 1

Antonio
Antonio

Reputation: 1

Correción: 3-LEN

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

Upvotes: 0

Newfave
Newfave

Reputation: 186

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.

Upvotes: 2

Rippo
Rippo

Reputation: 22424

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166586

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

Upvotes: 4

kevchadders
kevchadders

Reputation: 8335

Use the RIGHT function... e.g.

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

Upvotes: 4

Tomalak
Tomalak

Reputation: 338406

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

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

Upvotes: 33

Related Questions