Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Format Cast INT as Text Expand / Collapse
Author
Message
Posted Thursday, October 03, 2013 4:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:18 PM
Points: 504, Visits: 1,465
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?

Post #1501371
Posted Thursday, October 03, 2013 7:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501396
Posted Thursday, October 03, 2013 11:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:18 PM
Points: 504, Visits: 1,465
It's going to represent the number of seconds, so the possible range is 00 to 59.

Post #1501419
Posted Thursday, October 03, 2013 11:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:18 PM
Points: 90, Visits: 418
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)
Post #1501423
Posted Thursday, October 03, 2013 11:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:04 AM
Points: 581, Visits: 2,715
I just use a concatenate and a right.

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



Post #1501425
Posted Friday, October 04, 2013 8:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:13 PM
Points: 958, Visits: 1,285
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
Post #1501612
Posted Friday, October 04, 2013 8:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501618
Posted Monday, October 07, 2013 11:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:18 PM
Points: 504, Visits: 1,465
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!
Post #1502280
Posted Wednesday, October 09, 2013 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:28 PM
Points: 4, Visits: 77
A bit longer, but more obvious.

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

Pass the column name instead of @INT
Post #1503370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse