Format Cast INT as Text

  • 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?

  • 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
  • It's going to represent the number of seconds, so the possible range is 00 to 59.

  • 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)

  • I just use a concatenate and a right.

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

  • 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

  • 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
  • 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!

  • 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

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

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