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;
Improve this question
3
  • 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 Commented Aug 6, 2015 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. Commented Aug 6, 2015 at 21:35
  • More testing show that I'm wrong :) Commented Aug 6, 2015 at 21:43

3 Answers 3

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

Improve this answer
1
  • 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. Commented Aug 6, 2015 at 21:45
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.

Improve this answer
0

This is working.

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

Improve this answer
1
  • 1
    Can you give some explanation about that regex? Commented Feb 25 at 7:53

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.