Convert a varchar to Int? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert a varchar to Int?

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2010-06-15 : 16:07:14

Is it possible to convert a varchar to int? I've tried but I keep getting errors:

select top 1000 *
from Orders nolock
where type = 'SomeTypeTest'
and Convert(bigint, number) > 0
order by createDate desc

number is a varchar(25) so that's why I chose bigint. I get conversion errors:

Arithmetic overflow error converting expression to data type bigint.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 16:08:24

You've got data that doesn't fit into a bigint, hence the error. Can you eliminate that data as I assume it's bad data? I can't imagine needing a value bigger than what bigint supports.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-15 : 16:23:25

As an interim solution...You could try converting to decimal(38,0)

select top 1000 *
from Orders nolock
where type = 'SomeTypeTest'
and Convert(decimal(38,0), number) > 0
order by createDate desc

EDIT: Have a look at this thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129388

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2010-06-16 : 11:14:22

The varchar is a credit card number which is why it can be so long.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-16 : 12:49:43

Credit card numbers should fit into bigint. Here's what it supports (from BOL):

quote:

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 13:42:13

Interesting, not thought to store credit card number in INT.

Does the spec not allow for leading zero?

Leading zero definitely possibly on CVV2 number (which can be 3 [most cards] or 4 digits [Amex])

We store any punctuation (spaces etc.) that the user enters for convenience/readability, so need VARCHAR anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources