Oracle regexp_like square brackets inside character set

Question

This questions is in regards to Oracle, pl/sql, and the regexp_like function.

I am trying to build a character set that will match on all typical special characters. My character set currently looks like:

pattern := '[-~`!@#$%^&*\(\)\\{}_+=|''";:,./?]+';

I would like to add the square brackets to this character set, however, whatever I try to add ']' is not working. Here is a simple example that illustrates the problem:

select
 case when regexp_like('w]ord', '[\]]+') then 'true'
 else 'false' end
from dual;

This returns false, meaning it did not match the ']' character. Curiously, I can get the '[' character to match because this returns true:

select
 case when regexp_like('w[ord', '[\[]+') then 'true'
 else 'false' end
from dual;

• a1ex07: I don't think backslash is needed for escaping "]". select case when regexp_like('w[ord', '[]]+') then 'true' else 'false' end from dual; works just fine
• Elijah W. Gagne: @a1ex07 I could be wrong, but I think []]+ is getting matched because [] is an empty character set, and then ]+ is a closing square bracket. So it's correct that it matches, but the closing square bracket is not in the character set. I need it in the character set.
• Elijah W. Gagne: More testing show that I'm wrong :)

Answer 1

I don't think the backslash has a special meaning within the brackets. You can either use it as it is: regexp_like('a]b','[]]') or use an or: regexp_like('a]b','([whatever]|\])').

Any reason you can't use regexp_like('a]b','[^[:alnum:]]')?

• Elijah W. Gagne: Yep, I see that now [^[:alnum:]] seems like a good option, but it may match more than I'm willing to accept. Using the pattern: [][~`!@#$%^&*()\{}_+=|''";:,./?-]+, seem to do exactly what I want, thanks.

Answer 2

Why don`t you dip into the manual SQL Language Reference, Appendix D, Oracle Regular Expression Support:

[]

Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.

To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).

To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.

Answer 3

This is working.

select case when regexp_like('word]','[][@<~!#$%^*()+=[;:{}&|"?`>.'']') then 'true' else 'false' end from dual;

• Dominique: Can you give some explanation about that regex?