在sql中将指数转换为数字 [英] Convert exponential to number in sql

问题描述

我有大量的卡片令牌(16 位)从 xml 文件上传到 sql-server.问题是我将它们视为表达式，示例如下:

I have a large amount of card tokens (16 digits) uploaded from xml file to sql-server. The problem is I see them as expression, sample below:

3.3733E+15
3.3737E+15
3.3737E+15
3.3737E+15
3.37391E+15
3.37391E+15
3.37398E+15
3.37453E+15
3.37468E+15
3.37468E+15
3.3747E+15
3.37486E+15
3.37486E+15
3.37567E+15
3.3759E+15
3.3759E+15

有什么建议可以将它们更改为 16 位数字?我尝试更改数据类型，但出现错误将 varchar 值 '3.37201E+15' 转换为数据类型 int 时转换失败"

Any suggestion to change them to a 16 digit number? I have tried to change the data type, but got error"Conversion failed when converting the varchar value '3.37201E+15' to data type int"

感谢您的帮助！

@X.L.Ant 请参阅下面的代码.我从另一个表创建了这个表，它只是纯粹从 xml 文件中插入的.这是否会因为列 TOKEN 中的某些行为空而导致错误?

@X.L.Ant see my code below. I create this table from another one, which is just purely inserted from xml file. Is this may cause an error because some rows are empty in column TOKEN?

 CREATE TABLE MULTICURRENCY_CHECK
    (
    TOKEN varchar(255)
    )
    /*Merges all card tokens into 1 column, as in xml they are spread across different columns*/                                
    INSERT INTO MULTICURRENCY_CHECK
    (
    TOKEN
    )
    SELECT no FROM gpstransactionsnew2
        UNION ALL
    SELECT no19 FROM gpstransactionsnew2
        UNION ALL
    SELECT no68 FROM gpstransactionsnew2
        UNION ALL
    SELECT no93 FROM gpstransactionsnew2
        UNION ALL
    SELECT no107 FROM gpstransactionsnew2
        UNION ALL
    SELECT no121 FROM gpstransactionsnew2

    SELECT REPLACE(TOKEN, 'OW1', ' ')
    FROM MULTICURRENCY_CHECK

    /*Converts exponential expression to number*/
    SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
    FROM MULTICURRENCY_CHECK

推荐答案

在转换之前尝试将字符串强制转换为浮点:

Try to cast your string to float before converting it :

SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
FROM MULTICURRENCY_CHECK

• 参见这个小提琴.

我不知道您的 XML 源中这些数字的格式是什么，但是根据您提供的数据，您最终会得到 33733，例如后跟一堆零.如果您在 XML 中有更高的精度，也许您应该调整导入设置以保持这种精度，而不是尝试在 DB 中处理.

I don't know what's the format of those numbers in your XML source, but with the data you provide, you'll end up with 33733 for instance followed by a bunch of zeroes. If you have a bigger precision in your XML, maybe you should tweak your importing settings to keep this precision instead of trying to deal with that in the DB.

尝试使用 ISNUMERIC 测试您的字符串，以避免出现转换错误.添加列的原始输出将允许您检查哪个值无法转换(即转换为 0).

Try testing your strings with ISNUMERIC to avoid the casting errors you're getting. Adding a raw output of your column will allow you to check which value fails to convert (i.e. converts to 0).

SELECT TOKEN, 
       CONVERT(NUMERIC(16, 0), CAST(CASE 
                                      WHEN ISNUMERIC(TOKEN) = 1
                                      THEN TOKEN 
                                      ELSE 0 
                                    END AS FLOAT)) 
FROM   MULTICURRENCY_CHECK

这篇关于在sql中将指数转换为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！