HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Oracle regexp_like square brackets inside character set

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
characterbracketssquareoracleregexp_likesetinside

Problem

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;

Solution

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.

Context

StackExchange Database Administrators Q#109288, answer score: 4

Revisions (0)

No revisions yet.