SQL Server中如何确定不能转换为(decimal,float,int)的字段值

2021-12-10 00:00:00 casting tsql sql-server

我有一个 SQL Server 数据库.

I have a SQL Server database.

一个字段的值类似于

 ID      VALUE
  1      NEGATIF
  2      11.4
  3      0.2
  4      A RH(+)
  5      -----
  6      >>>>>
  7      5.6<
  8      -13.9

我想将 VALUE 字段转换为十进制，当然是可转换的字段.

I want to CONVERT VALUE field to decimal, of course convert-able fields.

  1. 什么样的 SQL 语句可以做到这一点?

  1. What kind of SQL statement can do this?

我如何了解转换时哪个值引发错误?

How can I understand which value is raising error while converting?

PS:我认为这可以解决 WHERE VALUE LIKE '[a-z]' 但如何添加更多过滤器，如 [-+ ()] ?

PS: I think this can solve WHERE VALUE LIKE '[a-z]' but how can I add more filter like [-+ ()] ?

推荐答案

Plain ISNUMERIC is rubbish

Plain ISNUMERIC is rubbish

  • 空字符串、+、- 和 .都是有效的
  • + 也是如此. 等等
  • 1e-3 对浮点数有效，但对十进制数无效(除非您先将浮点数转换为十进制数)
  • Empty string, +, - and . are all valid
  • So is +. etc
  • 1e-3 is valid for float but not decimal (unless you CAST to float then to decimal)

对于特别神秘但故障安全的解决方案，附加 e0 或 .0e0 then 使用 ISNUMERIC

For a particularly cryptic but failsafe solution, append e0 or .0e0 then use ISNUMERIC

SELECT
   ISNUMERIC(MyCOl + 'e0')   --decimal check,
   ISNUMERIC(MyCOl + '.0e0')  --integer check

所以

SELECT
    ID, VALUE,
    CAST(
          CASE WHEN ISNUMERIC(VALUE + 'e0') = 1 THEN VALUE ELSE NULL END
          AS decimal(38, 10)
        ) AS ConvertedVALUE
FROM
    Mytable

相关文章