SQL 将 nvarchar 转换为浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42539657/
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
SQL convert nvarchar to float
提问by Amir
I have a table with a column Quantity; in the original table this column is defined as nvarchar(100)so I need to cast it as float to be able to do some analysis:
我有一个带列的表Quantity；在原始表中，此列被定义为nvarchar(100)因此我需要将其转换为浮点数才能进行一些分析：
CAST([Quantity] AS FLOAT) AS Quantity
The issue is that I have some values which can not be converted to float like No-Quantity, Returnetc. I to have filter to exclude these values and then convert rest to float.On option is use where clause:
问题是，我有一些值不能转换为浮动像No-Quantity，Return等我有过滤，除去这些值，然后转换其余float.On选择是使用WHERE子句：
WHERE Quantity IN ('Return', 'Sales')
This is not the best way since if we have anew values in the original table then I need to figure out what it is and add it to the where clause.
这不是最好的方法，因为如果我们在原始表中有新的值，那么我需要弄清楚它是什么并将其添加到 where 子句中。
I am wondering is there is better way to identify non-convertible values?
我想知道是否有更好的方法来识别不可转换的值？
采纳答案by Gordon Linoff
In any database, you can use cast()and something like this:
在任何数据库中，您都可以使用cast()和这样的东西：
(case when quantity not in ('No-Quantity', 'Return', . . .)
then CAST([Quantity] as float)
end) as Quantity
The inlist would be the list of known string values.
该in列表将是已知字符串值的列表。
You can also do a fast-and-dirty check like this:
您还可以像这样进行快速而肮脏的检查：
(case when left(quantity, 1) between '0' and '1'
then CAST([Quantity] as float)
end) as Quantity
(Note: you may need to use substr()or substring()instead of left().)
（注意：您可能需要使用substr()或substring()代替left()。）
And, in general, any specific database has specific functions that can help with the conversion, such as try_convert()mentioned in a comment.
而且，一般来说，任何特定的数据库都具有可以帮助转换的特定功能，例如try_convert()评论中提到的。
回答by CSmith
Ifyour SQL Server supports TRY_CONVERT, this could provide a nice solution:
如果您的 SQL Server 支持TRY_CONVERT，这可以提供一个很好的解决方案：
SELECT TRY_CONVERT (float, [Quantity]) ...
will give you the converted values or NULL depending on the input. This could be helpful if you don't have strict control over the data.
将根据输入为您提供转换后的值或 NULL。如果您对数据没有严格控制，这可能会有所帮助。
回答by Horaciux
Another way (if you can't use TRY_CONVERT)
另一种方式（如果你不能使用TRY_CONVERT）
SELECT CAST(quantity AS float)
FROM myTable
WHERE IsNumeric(quantity) = 1 AND quantity IS NOT NULL
