SQL 将 varchar 转换为 float IF ISNUMERIC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6092588/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert varchar to float IF ISNUMERIC
提问by Pod Mays
I have a column that contains characters and numbers
我有一列包含字符和数字
12
13
14
19K/YR
22
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.
所以列类型是varchar。但是我也在用这个列做一些计算，所以我试图将数据转换为浮点数，如果它是数字的话。
This gives me an error though:
不过，这给了我一个错误：
SELECT CASE ISNUMERIC(QTY)
WHEN 1 THEN CAST(QTY AS float)
ELSE QTY
END AS test
回答by Mikael Eriksson
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.
您不能强制转换为浮动并将字符串保留在同一列中。当 isnumeric 返回 0 时，您可以这样做以获取 null。
SELECT CASE ISNUMERIC(QTY) WHEN 1 THEN CAST(QTY AS float) ELSE null END
回答by gbn
..extending Mikaels' answers
..扩展 Mikaels 的回答
SELECT
CASE WHEN ISNUMERIC(QTY + 'e0') = 1 THEN CAST(QTY AS float) ELSE null END AS MyFloat
CASE WHEN ISNUMERIC(QTY + 'e0') = 0 THEN QTY ELSE null END AS MyVarchar
FROM
...
- Two data types requires two columns
- Adding
e0fixes some ISNUMERIC issues(such as+-.and empty string being accepted)
- 两种数据类型需要两列
- 添加
e0修复了一些 ISNUMERIC问题（例如+-.接受空字符串）
回答by LDawggie
I found this very annoying bug while converting EmployeeID values with ISNUMERIC:
我在使用 ISNUMERIC 转换 EmployeeID 值时发现了这个非常烦人的错误：
SELECT DISTINCT [EmployeeID],
ISNUMERIC(ISNULL([EmployeeID], '')) AS [IsNumericResult],
CASE WHEN COALESCE(NULLIF(tmpImport.[EmployeeID], ''), 'Z')
LIKE '%[^0-9]%' THEN 'NonNumeric' ELSE 'Numeric'
END AS [IsDigitsResult]
FROM [MyTable]
This returns:
这将返回：
EmployeeID IsNumericResult MyCustomResult
---------- --------------- --------------
0 NonNumeric
00000000c 0 NonNumeric
00D026858 1 NonNumeric
(3 row(s) affected)
Hope this helps!
希望这可以帮助！
回答by Hyman.mike.info
-- TRY THIS --
- 尝试这个 -
select name= case when isnumeric(empname)= 1 then 'numeric' else 'notmumeric' end from [Employees]
But conversion is quit impossible
但转换是不可能的
select empname=
case
when isnumeric(empname)= 1 then empname
else 'notmumeric'
end
from [Employees]
