SQL 在sql中将指数转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29149910/
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
Convert exponential to number in sql
提问by Lukas
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:
我有大量的卡片令牌（16 位）从 xml 文件上传到 sql-server。问题是我将它们视为表达式，示例如下：
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
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"
有什么建议可以将它们更改为 16 位数字？我试图更改数据类型，但出现错误"Conversion failed when converting the varchar value '3.37201E+15' to data type int"
Thanks for help!
感谢帮助！
Edit:
编辑：
@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?
@XLAnt 请参阅下面的代码。我从另一个表创建了这个表，它只是纯粹从 xml 文件中插入的。这是否会因为列 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
回答by xlecoustillier
Try to cast your string to float before converting it :
在转换之前尝试将您的字符串转换为浮动：
SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
FROM MULTICURRENCY_CHECK
- See this fiddle.
- 看到这个小提琴。
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.
我不知道您的 XML 源中这些数字的格式是什么，但是根据您提供的数据，您最终会得到 33733，例如后跟一堆零。如果您在 XML 中有更高的精度，也许您应该调整导入设置以保持这种精度，而不是尝试在数据库中处理它。
EDIT:
编辑：
Try testing your strings with ISNUMERICto 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).
尝试测试您的字符串ISNUMERIC以避免您遇到的转换错误。添加列的原始输出将允许您检查哪个值无法转换（即转换为 0）。
SELECT TOKEN,
CONVERT(NUMERIC(16, 0), CAST(CASE
WHEN ISNUMERIC(TOKEN) = 1 THEN
TOKEN
ELSE 0
END AS FLOAT))
FROM MULTICURRENCY_CHECK
回答by Shekhar Prasad Rajak
SELECT colmn_name || '' FROM table_name
This should work.
这应该有效。
