convert varchar to int

  • Hi,

    I want to convert a varchar datatype to int. The varchar column actually contains decimal values. for example

    varchar Column

    -------------

    36.00

    20.00

    35.00

    60.00

    ....

    ...

    I want it to be converted to

    Int Column

    ----------

    36

    20

    35

    60

    ....

    ...

    right now I was doing like this

    cast((round(cast(qty as float),0)) as int). Is this a better way? If not please let me know the better way.

    Thanks,

    Sridhar!!

  • I don't see any problems with your code.  You "may" not need round, if in fact your data just shows zeros in the decimal places.  But otherwise it looks fine. 

     

    I have always used CONVERT.  Is there an advantage to using CAST over CONVERT? 

    I wasn't born stupid - I had to study.

  • As Farrell suggested, you don't need to do the rounding thing because INT will do the rounding for you.

    Either of these will work...

    SELECT CONVERT(INT,CONVERT(MONEY,'27.49'))
    SELECT CAST(CAST('26.50' AS MONEY) AS INT)

    Obviously, you would substitute the column name for the string literal in both of the above statements.

    Just a couple of side notes...

    Although an intermediate conversion to float will certainly work in this case, you should be advised that FLOAT does it's calculations based on Binary rather than Decimal numbering and the results will not always be what you would expect.

    INT will round up at the .50 mark and round down at the .49 mark.

    In case anyone else reads this, the reason two conversions are necessary is because the decimal point causes the following error when trying to convert a VARCHAR representation of a number with a decimal point in it, directly to INT.

    SELECT CONVERT(INT,'27.49')
    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value '27.49' to a column of data type int.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is there an advantage to using CAST over CONVERT?

    CAST is the ANSI way of doing things. IIRC, even MS recommends this over CONVERT. Though only CONVERT allows for the style parameter for DATETIMES. I guess this is a case for the inconsequent category

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Or

    SELECT CAST(FLOOR(Qty) AS int)

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 5 posts - 1 through 4 (of 4 total)

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