convert varchar to int

Question

convert varchar to int

Sridhar-137443

Hall of Fame

Points: 3762
More actions
January 7, 2005 at 1:40 pm

#162254

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

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Or

SELECT CAST(FLOOR(Qty) AS int)

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