将 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
相关文章