SQL 在视图中转换科学记数法（从 varchar -> numeric）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6750021/
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
Casting Scientific Notation (from varchar -> numeric) in a view
提问by Lance Perry
For reasons I can not help I have a varchar column with data like the following: 820.0E-12, 10.0E+00.
由于某些原因，我不禁有一个 varchar 列，其中包含如下数据：820.0E-12、10.0E+00。
I want the numeric value. So I have this testquery which works:
我想要数值。所以我有这个有效的测试查询：
declare @d varchar(256)
set @d = '820.0E-12'
select
CASE
WHEN @d like '%E-%' THEN LTRIM(RTRIM(CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18))))
WHEN @d like '%E+%' THEN NULL
ELSE @d
END
My result is: 0.000000000820000000(which is what I want)
我的结果是：0.000000000820000000（这是我想要的）
I change my SQL to account for the numbers > 0 (10.0E+00) like this:
我更改我的 SQL 以考虑数字 > 0 (10.0E+00)，如下所示：
WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)
My result changes to: 8.2E-10(which is NOT what I want)
我的结果更改为：8.2E-10（这不是我想要的）
If I change @d='10.0E+00'then I get 10(which is correct).
如果我更改@d='10.0E+00'那么我得到10（这是正确的）。
I've got a view that I need to make the output from a varchar column, that contains scientific notation, casted/converted into decimal(18,18).
我有一个观点，我需要从包含科学记数法的 varchar 列进行输出，转换/转换为十进制（18,18）。
Can somebody tell me what craziness is going on here?
有人能告诉我这里发生了什么疯狂的事情吗？
Or, maybe my question should be, how do I cast/convert a varchar scientific notation column to decimal output in a view?
或者，也许我的问题应该是，如何在视图中将 varchar 科学记数法列转换/转换为十进制输出？
My first WHEN statement works for numbers < 0 but I also need to account for numbers > 0. When I change the second WHEN, to include the CAST, it breaks/gives the wrong result.
我的第一个 WHEN 语句适用于数字 < 0，但我还需要考虑数字 > 0。当我更改第二个 WHEN 以包含CAST 时，它会中断/给出错误的结果。
回答by ean5533
There's a couple different problems all coming together here at the same time. Let's look at some of them:
有几个不同的问题同时出现在这里。让我们来看看其中的一些：
You're casting numbers as DECIMAL(18, 18). What that means is "give me a number that has room for a TOTAL of 18 characters, and 18 of them should be after the decimal". That works fine as long as your number is smaller than 0 (which is true for all E- numbers) but it will break if you try to use it on numbers > 0. For numbers > 0, just cast as DECIMAL without specifying anything else.
In the case where you add "WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)", you're getting different results for numbers < 0 because the engine is implicitly casting the result differently. I don't know the rules on how sql server decides to cast CASE results, but apparently making your proposed change causes the engine to recast it in a different way. Explicitly casting those results as decimal fixes the issue.
You need to LTRIM and RTRIM your results consistently. You can either add LTRIM and RTRIM to each case statement, or you can just LTRIM and RTRIM the results of the case.
您将数字转换为 DECIMAL(18, 18)。这意味着“给我一个可以容纳 18 个字符的数字，其中 18 个应该在小数点之后”。只要您的数字小于 0（对于所有 E- 数字都是如此），它就可以正常工作，但是如果您尝试在数字 > 0 上使用它，它就会中断。对于数字 > 0，只需将其转换为 DECIMAL 而不指定任何其他内容.
在您添加“WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)”的情况下，您会得到不同的数字 < 0 结果，因为引擎以不同的方式隐式转换结果。我不知道 sql server 如何决定转换 CASE 结果的规则，但显然进行您提议的更改会导致引擎以不同的方式重新转换它。将这些结果显式转换为十进制可解决此问题。
您需要始终如一地对结果进行 LTRIM 和 RTRIM。您可以将 LTRIM 和 RTRIM 添加到每个 case 语句中，或者您可以只 LTRIM 和 RTRIM 的案例的结果。
Here's a solution that should totally solve everything:
这是一个应该完全解决所有问题的解决方案：
SELECT
LTRIM(RTRIM(CASE
WHEN @d like '%E-%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18))
WHEN @d like '%E+%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL)
ELSE @d
END))
回答by Leonardo Marques de Souza
you can use ISO "real" datatype
您可以使用 ISO“真实”数据类型
SELECT convert(numeric(18,18),convert(real,'820.0E-12'))
--OR with more precision
SELECT convert(numeric(18,18),convert(float(53),'820.0E-12'))
回答by Rick James
mysql> select '820.0E-12' + 0, '10.0E+00' + 0;
+-----------------+----------------+
| '820.0E-12' + 0 | '10.0E+00' + 0 |
+-----------------+----------------+
| 0.00000000082 | 10 |
+-----------------+----------------+
That is, simply adding 0 to the varchar should give you a numeric value. (OK, you might need a TRIMfirst.)
也就是说，简单地将 0 添加到 varchar 应该会给你一个数值。（好吧，你可能需要TRIM第一个。）
There is essentially no reasonable use case for the mor nin FLOAT(m,n); simply declare things FLOAT(for up to about 7 significant digits) or DOUBLE(for about 16).
m或nin基本上没有合理的用例FLOAT(m,n)；只需声明事物FLOAT（最多约 7 个有效数字）或DOUBLE（约 16 个）。
