如何确定SQL Server中不能转换为(十进制、浮点、int)的字段值

时间:2021-12-03 17:08:28

I have a SQL Server database.

我有一个SQL Server数据库。

One field has values which are like

一个字段有类似的值。

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

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

我想把VALUE字段转换成decimal,当然是可转换字段。

  1. What kind of SQL statement can do this?

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

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

    我如何理解转换时哪个值引起错误?

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

PS:我认为这可以解决“[a-z]”之类的值,但是我如何添加更多的[-+()]这样的过滤器呢?

1 个解决方案

#1


59  

Plain ISNUMERIC is rubbish

平原ISNUMERIC是垃圾

  • 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)
  • 1 -3适用于浮点数,但不适用于十进制数(除非您将浮点数转换为浮点数,然后将其转换为十进制数)

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

对于一个特别神秘但故障安全的解决方案,附加e0或.0e0,然后使用ISNUMERIC

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

So

所以

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

#1


59  

Plain ISNUMERIC is rubbish

平原ISNUMERIC是垃圾

  • 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)
  • 1 -3适用于浮点数,但不适用于十进制数(除非您将浮点数转换为浮点数,然后将其转换为十进制数)

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

对于一个特别神秘但故障安全的解决方案,附加e0或.0e0,然后使用ISNUMERIC

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

So

所以

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