SQL 如何将 nvarchar 转换为十进制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5487760/
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
how to cast nvarchar to decimal?
提问by loki
i know how to convert nvarchar to decimal(18,4) Cast method. my tables rows count 80000. My query run perfect below...
我知道如何将 nvarchar 转换为 decimal(18,4) Cast 方法。我的表行数为 80000。我的查询在下面完美运行...
SELECT top 80000 id, Cast(MH as decimal(18,4)) as MH
FROM TaskRelations WHERE MH is not null
BUT;
但;
but below select query not WORK! if write below:
但下面选择查询不工作！如果写在下面：
SELECT id, Cast(MH as decimal(18,4)) as MH
FROM TaskRelations WHERE MH is not null
ERROR: Error converting data type nvarchar to numeric.
错误：将数据类型 nvarchar 转换为数字时出错。
回答by JNK
You have non-numeric data in your MHfield after row 80,000.
MH在第 80,000 行之后，您的字段中有非数字数据。
You can use the ISNUMERICfunctionbut it is notoriously buggy and will give you loads of false positives depending on your data.
您可以使用该ISNUMERIC功能，但它是出了名的错误，并且会根据您的数据为您提供大量误报。
回答by SWeko
The problem is usually that indeed some of the nvarchars in the MH columns are not convertible to decimal.
问题通常是 MH 列中的某些 nvarchars 确实无法转换为十进制。
You can try to inspect the data visually, but as you have more than 80k recordsm, that might not be feasible. Try to inspect the data in some other way - you can use isNumeric, or use group byon the first (or last) three characters of the column, to find the erring data.
您可以尝试直观地检查数据，但由于您有超过 8 万条记录，这可能不可行。尝试以其他方式检查数据 - 您可以使用isNumeric，或group by在列的前（或最后）三个字符上使用，以查找错误数据。
回答by Beth
try this:
尝试这个：
SELECT max(MH) as max_mh, min(mh) as min_mh
FROM TaskRelations
WHERE MH is not null
the non-numeric values should be lower or higher than the numeric ones.
非数字值应低于或高于数字值。
回答by John Hartsock
Basically the error your receiving is due to the fact that some record past the top 80000 records cannot be converted to decimal. Check your data on all rows > 80000
基本上，您收到的错误是由于超过前 80000 条记录的某些记录无法转换为十进制。检查所有行的数据 > 80000
