oracle SQL*Loader - 如何忽略具有特定字符的某些行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2419182/
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
SQL*Loader - How can i ignore certain rows with a specific charactre
提问by ziggy
If i have a CSV file that is in the following format
如果我有以下格式的 CSV 文件
"fd!","sdf","dsfds","dsfd"
"fd!","asdf","dsfds","dsfd"
"fd","sdf","rdsfds","dsfd"
"fdd!","sdf","dsfds","fdsfd"
"fd!","sdf","dsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"fd!","sdf","dsfds","dsfd"
Is it possible to exclude any row where the first column has an exclamation mark at the end of the string. i.e. it should only load the following rows
是否可以排除第一列在字符串末尾带有感叹号的任何行。即它应该只加载以下行
"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
Thanks
谢谢
回答by dgaspar
According to the Loading Records Based on a Conditionsection of the SQL*Loader Control File Reference (11g):
根据SQL*Loader Control File Reference (11g) 的基于条件的加载记录部分：
"You can choose to load or discard a logical record by using the WHENclause to test a condition in the record."
“您可以通过使用WHEN子句测试记录中的条件来选择加载或丢弃逻辑记录。”
So you'd need something like this:
所以你需要这样的东西：
LOAD DATA ... INSERT INTO TABLE mytable WHEN mycol1 NOT LIKE '%!'
(mycol1.. ,mycol2 ..)
But the LIKEoperator is not available! You only have =and !=
但是LIKE运算符不可用！你只有 =和!=
Maybe you could try an External Table instead.
也许您可以尝试使用外部表。
回答by Gary Myers
I'd stick a CONSTRAINT on the table, and just let them be rejected. Maybe delete them after load. Or a unix "grep -v" to clear them out the file.
我会在桌子上贴一个 CONSTRAINT，然后让它们被拒绝。也许在加载后删除它们。或者使用 unix "grep -v" 来清除文件。
