How to include apostrophe in character set for REGEXP_SUBSTR()

Question

The IBM i implementation of regex uses apostrophes (instead of e.g. slashes) to delimit a regex string, i.e.:

... where REGEXP_SUBSTR(MYFIELD,'myregex_expression')

If I try to use an apostrophe inside a [group] within the expression, it always errors - presumably thinking I am giving a closing quote. I have tried:
- escaping it: \'
- doubling it: '' (and tripling)

No joy. I cannot find anything relevant in the IBM SQL manual or by google search.

I really need this to, for instance, allow names like O'Leary.

Answer 1

A single quote can be defined with the \x27 notation:

^[A-Z0-9_+\x27-]+
          ^^^^

Note that when you use a hyphen in the character class/bracket expression, when used in between some chars it forms a range between those symbols. When you used ^[A-Z0-9_\+-\x27]+ you defined a range between + and ', which is an invalid range as the + comes after ' in the Unicode table.