Format Cast INT as Text

Question

Format Cast INT as Text

dan-572483

SSCoach

Points: 15870
More actions
October 3, 2013 at 4:15 pm

#278633

I'm using CAST to conver integers to Varchar or Char, but when the value is less than 10 I want it to start with a 0, as in "07". But a 12 is still 12.
What is the best way to do that?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Are you just using integers with values from 0 to 99? Or Are you going to have values like '01', '12', '123', '57246' ? because you might want to add more zeroes.

Depending on your answer, the solution could be different.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dan-572483 SSCoach Points: 15870 More actions · Answer 2

It's going to represent the number of seconds, so the possible range is 00 to 59.

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 3

dan-572483 (10/3/2013)
It's going to represent the number of seconds, so the possible range is 00 to 59.

one way is like the below. There may be a better method but this is the way i would use it.

DECLARE @seconds INT = 5

DECLARE @Stringlength INT = 2

SELECT REPLACE(STR(@seconds,@Stringlength),' ','0')

-- output = 05

If you put anything greater than 9 it won't pad it with a 0 infront (e.g. 10 will be 10)

dogramone Hall of Fame Points: 3216 More actions · Answer 4

I just use a concatenate and a right.

So select right('00' + cast(1 as varchar) ,2)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 5

dogramone (10/3/2013)
I just use a concatenate and a right.
So select right('00' + cast(1 as varchar) ,2)

I've used this method quite often. Simple enough.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Just another way to do it. The Tally is just to generate the sample data and has nothing to do with the solution.

WITH Tally AS(

SELECT TOP 60 ROW_NUMBER() OVER(ORDER BY object_id) n

FROM sys.objects

)

SELECT RIGHT( 100 + n, 2)

FROM Tally

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dan-572483 SSCoach Points: 15870 More actions · Answer 7

dogramone (10/3/2013)
I just use a concatenate and a right.
So select right('00' + cast(1 as varchar) ,2)

Simple and makes sense. Thanks!

Aditya Daruka Old Hand Points: 339 More actions · Answer 8

A bit longer, but more obvious.

DECLARE @INTINT= 1 ;

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

Pass the column name instead of @INT