在 Oracle 正则表达式查询中转义单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19483374/
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
Escaping a single quote in Oracle regex query
提问by Nimbocrux
This is really starting to hurt!
这真的开始疼了！
I'm attempting to write a query in Oracle developer using a regex condition
我正在尝试使用正则表达式条件在 Oracle 开发人员中编写查询
My objective is to find all last names that contain charachters not commonly contained in names (non-alpha, spaces, hyphens and single quotes)
我的目标是找到所有包含名称中不常见的字符的姓氏（非字母、空格、连字符和单引号）
i.e. I need to find
即我需要找到
J00ls
McDonald "Macca"
Smithy (Smith)
and NOT find
并没有找到
Smith
Mckenzie-Smith
El Hassan
O'Dowd
My present query is
我目前的查询是
select * from dm_name
WHERE regexp_like(last_name, '([^A-Za-z -])')
and batch_id = 'ATEST';
which excludes everything expected except the single quote. When it comes to putting the single quote character, the Oracvel SQL Develoepr parser takes it as a literal.
它排除了除单引号之外的所有预期内容。在放置单引号字符时，Oracvel SQL Develoepr 解析器将其作为文字。
I've tried:
我试过了：
\' -- but got a "missing right parenthesis" error
||chr(39)|| -- but the search returned nothing
'' -- negated the previous character in the matching group e.g. '([^A-Za-z -''])' made names with '-' return.
I'd appreciate anything you could offer.
我很感激你能提供的任何东西。
采纳答案by Bob Jarvis - Reinstate Monica
The following works:
以下工作：
select *
from dm_name
WHERE regexp_like(last_name, '([^A-Za-z ''-])');
Whether SQL Developer will like it or not is something I cannot attest to as I don't have that product installed.
SQL Developer 是否会喜欢它是我无法证明的，因为我没有安装该产品。
Share and enjoy.
分享和享受。
回答by beny23
Just double the single quote to escape your quote.
只需将单引号加倍即可逃避您的报价。
So
所以
select *
from dm_name
where regexp_like(last_name, '[^A-Za-z ''-]')
and batch_id = 'ATEST'
See also this sqlfiddle. Note, I tried a similar query in SQL developer and that worked as well as the fiddle.
另请参阅此sqlfiddle。请注意，我在 SQL developer 中尝试了一个类似的查询，并且效果很好。
Note also, for this to work the -character has to be the last character in the group as otherwise it tries to find the group SPACEto 'rather than the character -.
另请注意，要使其工作，-字符必须是组中的最后一个字符，否则它会尝试找到组SPACEto'而不是字符-。
