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: Yesterday @ 10:51 AM
Points: 558, Visits: 1,652
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 4,046, Visits: 9,202
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: Yesterday @ 10:51 AM
Points: 558, Visits: 1,652
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 12:32 PM
Points: 611, Visits: 3,085
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: Tuesday, December 9, 2014 11:09 AM
Points: 989, Visits: 1,329
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 4,046, Visits: 9,202
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: Yesterday @ 10:51 AM
Points: 558, Visits: 1,652
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: Tuesday, December 2, 2014 3:36 PM
Points: 4, Visits: 84
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