3

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;
Elijah W. Gagne
  • 685
  • 1
  • 11
  • 21
  • 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 – a1ex07 Aug 06 '15 at 21:27
  • @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 Aug 06 '15 at 21:35
  • More testing show that I'm wrong :) – Elijah W. Gagne Aug 06 '15 at 21:43

2 Answers2

4

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.

Community
  • 1
miracle173
  • 7,404
  • 23
  • 41
2

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:]]')?

mustaccio
  • 20,465
  • 17
  • 49
  • 60
  • 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. – Elijah W. Gagne Aug 06 '15 at 21:45