convert str to int


Please assist how can we convert str to integer?

Select cast(Column1 as Int) From MyTable

Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
CLng() converts to Long Integer
CStr() converts to String
CDate() converts to Date
CBool() converts to Boolean
CCur() converts to Currency
CDbl() converts to Double

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT CInt(Column1) FROM MyTable
rockiroadsConnect With a Mentor Commented:
SELECT VAL(Column1) FROM MyTable
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

this will do

Select CInt(Column1) From MyTable
so fast!
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Off Topic)  <Frazier Crane> When you know the answer, it's hard not to hit the buzzer...
Ryan, is your data likely to contain nulls or letters?

I say this because VAL converts

Null becomes #Error
123ABC becomes 123
ABC123 becomes 0

Not sure what the conversion functions (as Jim as explained) might return. It may be a data type mismatch (no offence Jim)

So you need to cater for this scenario, thats if u are likely to have them

to stop nulls from falling over, u can wrap field with NZ


gives a null value to 0, but u can specify what u want

Chuck WoodCommented:

Just for your information, the C in CInt, CStr, CDate, etc. stands for Cast. I noticed you are familiar with cast.

Chuck:  VB Editor, Help, Answer Wizard, type - Conversion Functions - and you get all the conversion functions where specifically it talks about "coercing" one datatype to another.  Type in - Cast - and you get an entirely different set of returns, none of which are 'Conversion Functions'  - for what it's worth.
Chuck WoodCommented:
GRayL: I was reading a discussion of these functions in VB.Net and it said that the C was used to "cast" the variable into a different type. Sorry I don't remember where I saw that. In any case, it doesn't matter as cast and convert are fairly synonymous. For those used to cast in  other languages, perhaps it is helpful for them to think in terms of cast instead of convert. Thanks for your input.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.