Escaping a single quote in Oracle regex query

Question

This is really starting to hurt!

I'm attempting to write a query in Oracle developer using a regex condition

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.

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.

Answer 1

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.

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 SPACE to ' rather than the character -.