如何告诉 sqlldr 修剪尾随和前导空格
我正在使用 sqlldr 加载数据，其中某些字段具有尾随和前导空格。除了说之外，还有什么方法可以告诉 sqlldr 忽略这些空格吗
field "trim(:field)"
？
I'm using sqlldr to load data in which some of the fields have trailing and leading whitespaces. Is there a way to to tell sqlldr to ignore these whitespaces other than by saying
field "trim(:field)"
?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
字段“trim(:field)”在大多数情况下工作正常，但我想到了典型的情况，其中列大小为 char(1) 但控制文件中的数据为“Y”和“trim(:field)”加载数据失败。
经过大量研究后，我了解到，trim() 函数会修剪数据中的空格，但同时会在数据中添加 null 来代替空格，因此上面示例中的数据长度将是 2，因为它计算空值在数据的左侧。因此，数据将类似于 nullY，因为表中的列大小小于其未加载的实际大小，并且 Oracle 会抛出错误消息。
为了解决这个问题，我使用了：
"trim(null from trim(:field))"
上面的脚本首先修剪空格，然后再次修剪 sql-loader 附加的 null。
我希望我提供的信息对像我一样面临问题的人有所帮助。
我无法抗拒发布此内容，因为我没有找到任何线程回答此类问题。
field "trim(:field)" works fine in most of the case but i came up with typical case where the column size was char(1) but the data in control file was of ' Y' and "trim(:field)" failed to load the data.
After a lot of research i came to know that trim() function trims the white spaces from the data but at the same time adds null to data in place of white spaces so the data length in above example will be 2 as it counts null values on the left side of data. Thus, the data will be some thing like nullY since, the column size in table is less than the actual size its not loaded and oracle throws error message.
To overcome this issue i used:
"trim(null from trim(:field))"
Above script first trims the white spaces and then again trim the null appended by the sql-loader.
I hope information i have provided will be helpful to anyone facing the problem as i did.
I could not resist to post this as i did not find any thread answering this sort of issue.
我知道这是一个旧线程，但无论如何我都会插话。
与许多事情一样，答案是“视情况而定”。这取决于数据是固定格式还是分隔格式。如果进行分隔，还取决于控制文件是否使用 OPTIONALLY ENCLOSED BY 子句。有关所有肮脏的详细信息，请参阅 Oracle SQL*Loader 文档。例如，尝试以下链接： http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1007768
I know it's an old thread, but I'll chime in anyway.
As with many things, the answer is "it depends." It depends whether the data is fixed format or delimited. If delimited, it also depends whether the control files uses the OPTIONALLY ENCLOSED BY clause. For all the sordid details, see the Oracle SQL*Loader documentation. For example, try this link: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1007768