将 varchar 转换为 float IF ISNUMERIC

问题描述

我有一列包含字符和数字

I have a column that contains characters and numbers

12
13
14
19K/YR
22

所以列类型是varchar.但后来我也在用这个列做一些计算，所以我试图将数据转换为浮点数，如果它是数字.

So the column type is varchar. But then I'm also doing some computations with this column, so I'm trying to convert the data to float if it is numeric.

虽然这给了我一个错误:

This gives me an error though:

SELECT CASE ISNUMERIC(QTY) 
         WHEN 1 THEN CAST(QTY AS float) 
         ELSE QTY 
       END AS test

推荐答案

您不能强制转换为浮动并将字符串保留在同一列中.当 isnumeric 返回 0 时，您可以这样做以获取 null.

You can't cast to float and keep the string in the same column. You can do like this to get null when isnumeric returns 0.

SELECT CASE ISNUMERIC(QTY) WHEN 1 THEN CAST(QTY AS float) ELSE null END