SQL CAST CHAR AS INTEGER [英] SQL CAST CHAR AS INTEGER

问题描述

大家好



我有一个表格，其中一列是CHAR（10），用于存放'参考'。

如果系统自动创建记录，则引用是预定义的，并且始终是整数，但手动（用户）创建的记录可以包含任何值。

I需要在这个列上加入，但只有当它包含一个整数...



听起来很简单，但我似乎无法让它工作。



我最接近的是

 CAST（CASE WHEN ISNUMERIC（Ref_Column）= 1那么Ref_Column ELSE 0 END as INT）

然而，ISNUMERIC允许使用货币符号，因此当Ref_Column包含类似£123.45的错误

转换失败何时转换 varchar 值' £123.45'  to  data  type   int 。

任何人都有这方面的解决方案吗？



SQL 2008所以没有PARSE选项......



谢谢

解决方案

首先将它转换为金钱怎么样？

 声明  @ value   varchar （ 100 ）= '  

125'
选择 案例 当为数字时（ @ value ）= 1 然后 cast（cast（ @ value as money ） as int ） else 0 end

Hi All

I have a table with a column that is a CHAR(10) which is used to hold a 'reference'.
If a record is created automatically by the system then the reference is pre-defined and is always an integer but there are manually (user) created records which can have any value in them.
I need to JOIN on this column but only when it contains an integer...

Sounds easy but I cannot seem to get it to work.

The closest I got was

CAST( CASE WHEN ISNUMERIC(Ref_Column) = 1 THEN Ref_Column ELSE 0 END as INT)

However ISNUMERIC allows currency symbols so it fails when the Ref_Column contains something like '£123.45 ' with the error

Conversion failed when converting the varchar value '£123.45  ' to data type int.

Anyone got any slick solutions for this?

SQL 2008 so no PARSE option...

Thanks

解决方案

What about first converting it to money?

declare @value  varchar(100) = '

125' select case when isnumeric(@value) = 1 then cast(cast(@value as money) as int) else 0 end

这篇关于SQL CAST CHAR AS INTEGER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！