CODEWITHSUNDEEP
sql-server
PS078
PS078

Reputation: 461

How to overcome limitation of ISNUMERIC column "ERROR : overflowed an int column"

I have a requirement wherein I have to check whether value entered in VARCHAR(200) column is numeric or not, and if so a relevant error message should be passed.

I am validating the same with ISNUMERIC function since my column value is varchar so user can enter more than 10 characters as well due to which I am getting this error:

overflowed an int column

Because of the other business support, I can not change the data type of the column to int.

As of now I have implemented LEN() < 10 condition before checking ISNUMERIC but seeking if any alternate and better option available.

Upvotes: 0

Views: 762

Answers (1)

Dhaval
Dhaval

Reputation: 2379

If you work on Sql server 2012 Than its better to Use TRY_Convert() Function.it will give NULL as output rather than impose error 

declare @d varchar(200)='940852774565564'
if ((select ISNUMERIC(@d))=1)
select Try_Convert(@d as bigint)

else Convert the value to bigint rather than INT 

declare @d varchar(200)='940852774565564'
if ((select ISNUMERIC(@d))=1)
select cast(@d as bigint)

Upvotes: 1

Related Questions