在视图中转换科学记数法(来自 varchar -> numeric) [英] Casting Scientific Notation (from varchar -> numeric) in a view

问题描述

出于某些原因，我无法帮助我有一个 varchar 列，其中包含如下数据:820.0E-12、10.0E+00.

For reasons I can not help I have a varchar column with data like the following: 820.0E-12, 10.0E+00.

我想要数值.所以我有这个 test 查询有效:

I want the numeric value. So I have this test query 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

我的结果是:0.000000000820000000(这是我想要的)

My result is: 0.000000000820000000 (which is what I want)

我将我的 SQL 更改为说明 > 0 (10.0E+00) 的数字，如下所示:

I change my SQL to account for the numbers > 0 (10.0E+00) like this:

WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)

我的结果更改为:8.2E-10(这不是我想要的)

My result changes to: 8.2E-10 (which is NOT what I want)

如果我更改 @d='10.0E+00'，那么我会得到 10(这是正确的).

If I change @d='10.0E+00' then I get 10 (which is correct).

我有一个观点，我需要从包含科学记数法的 varchar 列进行输出，转换/转换为十进制 (18,18).

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 科学记数法列转换/转换为十进制输出?

我的第一个 WHEN 语句适用于数字 <0 但我还需要考虑大于 0 的数字.当我更改第二个 WHEN 以包含 CAST 时，它会中断/给出错误的结果.

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.

推荐答案

有几个不同的问题同时出现在这里.让我们看看其中的一些:

There's a couple different problems all coming together here at the same time. Let's look at some of them:

1. 您将数字转换为 DECIMAL(18, 18).这意味着给我一个可以容纳 18 个字符的数字，其中 18 个应该在小数点之后".只要您的数字小于 0(对于所有 E- 数字都是如此)，它就可以正常工作，但是如果您尝试在数字 > 0 上使用它，它就会中断.对于数字 > 0，只需将其转换为 DECIMAL 而不指定任何其他内容.

1. 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.

在添加WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)"的情况下，对于数字 < 会得到不同的结果.0 因为引擎以不同的方式隐式转换结果.我不知道 sql server 如何决定转换 CASE 结果的规则，但显然进行您提议的更改会导致引擎以不同的方式重新转换它.将这些结果显式转换为十进制可解决此问题.

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.

您需要始终如一地对结果进行 LTRIM 和 RTRIM.您可以在每个 case 语句中添加 LTRIM 和 RTRIM，也可以只对 case 的结果进行 LTRIM 和 RTRIM.

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.

这是一个应该完全解决所有问题的解决方案:

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))

这篇关于在视图中转换科学记数法(来自 varchar -> numeric)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！