如何克服ISNUMERIC列的限制“ERROR：溢出一个int列”

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.

我有一个要求,我必须检查在VARCHAR(200)列中输入的值是否为数字,如果是,则应传递相关的错误消息。

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:

我正在使用ISNUMERIC函数进行验证,因为我的列值是varchar,因此用户也可以输入10个以上的字符,因为我收到此错误:

overflowed an int column

溢出了一个int列

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

由于其他业务支持,我无法将列的数据类型更改为int。

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

截至目前,我已经在检查ISNUMERIC之前实现了LEN()<10条件,但是寻求是否有任何替代和更好的选择。

1 个解决方案

#1

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

如果你在Sql server 2012上工作比使用TRY_Convert()更好,Function.it会将NULL作为输出而不是强加错误

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

else将值转换为bigint而不是INT

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

#1