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 3, 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: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
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 3, 2013 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 3,813, Visits: 8,581
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501396
Posted Thursday, October 3, 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: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
It's going to represent the number of seconds, so the possible range is 00 to 59.

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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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 3, 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: Yesterday @ 11:06 PM
Points: 599, Visits: 3,003
I just use a concatenate and a right.

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



Post #1501425
Posted Friday, October 4, 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: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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 4, 2013 8:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 3,813, Visits: 8,581
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501618
Posted Monday, October 7, 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: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
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 9, 2013 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:43 PM
Points: 4, Visits: 83
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