您如何从 Oracle SQL 中的科学记数法转换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9505369/
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 do you convert from scientific notation in Oracle SQL?
提问by ScrappyDev
We are trying to load a file created by FastExport into an oracle database.
However the Float column is being exported like this: 1.47654345670000000000 E010.
我们正在尝试将 FastExport 创建的文件加载到 oracle 数据库中。
但是浮动栏被导出这样的：1.47654345670000000000 E010。
How do you configure SQL*Loader to import it like that.
你如何配置 SQL*Loader 来导入它。
Expecting Control Script to look like:
期望控制脚本看起来像：
OPTIONS(DIRECT=TRUE, ROWS=20000, BINDSIZE=8388608, READSIZE=8388608)
UNRECOVERABLE LOAD DATA
infile 'data/SOME_FILE.csv'
append
INTO TABLE SOME_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols (
FLOAT_VALUE CHAR(38) "???????????????????",
FILED02 CHAR(5) "TRIM(:FILED02)",
FILED03 TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6",
FILED04 CHAR(38)
)
I tried to_number('1.47654345670000000000 E010', '9.99999999999999999999 EEEE')
我试过 to_number('1.47654345670000000000 E010', '9.99999999999999999999 EEEE')
Error:
ORA-01481: invalid number format modelerror.
错误：
ORA-01481: invalid number format model错误。
I tried to_number('1.47654345670000000000 E010', '9.99999999999999999999EEEE')
我试过 to_number('1.47654345670000000000 E010', '9.99999999999999999999EEEE')
Error:
ORA-01722: invalid number
错误：
ORA-01722: invalid number
These are the solutions I came up with in order of preference:
这些是我按优先顺序提出的解决方案：
to_number(replace('1.47654345670000000000 E010', ' ', ''))to_number(TRANSLATE('1.47654345670000000000 E010', '1 ', '1'))
to_number(replace('1.47654345670000000000 E010', ' ', ''))to_number(TRANSLATE('1.47654345670000000000 E010', '1 ', '1'))
I would like to know if there are any better performing solutions.
我想知道是否有任何性能更好的解决方案。
回答by Alex Poole
As far as I'm aware there is no way to have to_numberignore the space, and nothing you can do in SQL*Loader to prepare it. If you can't remove it by pre-processing the file, which you've suggested isn't an option, then you'll have to use a string function at some point. I wouldn't expect it to add a huge amount of processing, above what to_number will do anyway, but I'd always try it and see rather than assuming anything - avoiding the string functions sounds a little like premature optimisation. Anyway, the simplest is possibly replace:
据我所知，无法to_number忽略该空间，并且您无法在 SQL*Loader 中做任何准备来准备它。如果您不能通过预处理文件来删除它，您建议这不是一个选项，那么您将不得不在某个时候使用字符串函数。我不希望它增加大量的处理，超过 to_number 无论如何都会做的事情，但我总是尝试并查看而不是假设任何事情 - 避免字符串函数听起来有点像过早的优化。无论如何，最简单的可能是replace：
select to_number(replace('1.47654345670000000000 E010',' ',''),
'9.99999999999999999999EEEE') from dual;
or just for display purposes:
或仅用于显示目的：
column num format 99999999999
select to_number(replace('1.47654345670000000000 E010',' ',''),
'9.99999999999999999999EEEE') as num from dual
NUM
------------
14765434567
You could define your own function to simplify the control file slightly, but not sure it'd be worth it.
您可以定义自己的函数来稍微简化控制文件，但不确定是否值得。
Two other options come to mind. (a) Load into a temporary table as a varchar, and then populate the real table using the to_number(replace()); but I doubt that will be any improvement in performance and might be substantially worse. Or (b) if you're running 11g, load into a varcharcolumn in the real table, and make your number column a virtual columnthat applies the functions.
我想到了另外两个选项。(a) 作为 加载到临时表中varchar，然后使用 填充真实表to_number(replace())；但我怀疑这是否会提高性能，而且可能会更糟。或者 (b) 如果您正在运行 11g，则加载到varchar实际表中的一列中，并使您的数字列成为应用这些函数的虚拟列。
Actually, a third option... don't use SQLLoader at all, but use the CSV file as an external table, and populate your real table from that. You'll still have to do the to_number(replace())but you might see a difference in performance over doing it in SQLLoader. The difference could be that it's worse, of course, but might be worth trying.
实际上，第三个选项...根本不使用 SQL Loader，而是使用 CSV 文件作为外部表，并从中填充您的真实表。您仍然必须执行此操作，to_number(replace())但您可能会发现与在 SQLLoader 中执行此操作相比，性能有所不同。当然，不同之处可能在于它更糟，但可能值得一试。
回答by Add080bbA
Change number width with "set numw"
使用“set numw”更改数字宽度
select num from blabla >
从 blabla 中选择 num >
result >> 1,0293E+15
结果 >> 1,0293E+15
set numw 20;
设置 numw 20;
select num from blabla >
从 blabla 中选择 num >
result >> 1029301200000021
结果>> 1029301200000021
回答by ScrappyDev
Here is the solution I went with:
这是我使用的解决方案：
OPTIONS(DIRECT=TRUE, ROWS=20000, BINDSIZE=8388608, READSIZE=8388608)
UNRECOVERABLE LOAD DATA
infile 'data/SOME_FILE.csv'
append
INTO TABLE SOME_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols (
FLOAT_VALUE CHAR(38) "REPLACE(:FLOAT_VALUE,' ','')",
FILED02 CHAR(5) "TRIM(:FILED02)",
FILED03 TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6",
FILED04 CHAR(38)
)
In my solution the conversion to a number is implicit:
"REPLACE(:FLOAT_VALUE,' ','')"
在我的解决方案中，转换为数字是隐式的：
"REPLACE(:FLOAT_VALUE,' ','')"
回答by Jana-guest
In Oracle 11g, it's not needed to convert numbers specially.
在 Oracle 11g 中，不需要专门转换数字。
Just use integer externalin the .ctl-file:
只需integer external在.ctl-file 中使用：
I tried the following in my Oracle DB:
我在我的 Oracle DB 中尝试了以下操作：
field MYNUMBER has type NUMBER.
Inside .ctl-file I used the following definition:
在.ctl-file 中，我使用了以下定义：
MYNUMBER integer external
In the datafile the value is: MYNUMBER: -1.61290E-03
在数据文件中的值为： MYNUMBER: -1.61290E-03
As for the result: sqlldr loaded the notation correctly: MYNUMBER field: -0.00161290
至于结果：sqlldr 正确加载了符号： MYNUMBER field: -0.00161290
I am not sure if it's a bug or a feature; but it works in Oracle 11g.
我不确定这是错误还是功能；但它适用于 Oracle 11g。
