CODEWITHSUNDEEP
sqlregexoracle-database
flyaround
flyaround

Reputation: 363

How to escape ] (bracketright) in a Oracle SQL regexp_like query?

To select rows which are using one of several special characters, I would like to use the regexp_like condition in my Oracle SQL query:

    select 1 from dual where
    regexp_like(
      'Example [0,5%] item with special characters', 
      '[\°\]\[]+'
    );

But this is not working for the bracket right character (']').

I already tried:

but without success. It seems that it is working for any other character but not with the bracket right.

Is there a way to fix this query or have someone an explanation why escaping this character is not working?

Upvotes: 1

Views: 2452

Answers (3)

Nick
Nick

Reputation: 147216

From the Oracle documentation:

To specify a right bracket (]) in the bracket expression, place it first in the list

So your query should look like (note none of the characters needs to be escaped in the character class):

select 1 from dual where
regexp_like(
  'Example [0,5%] item with special characters', 
  '[][°]'
)

Output

1

Demo on SQLFiddle

Upvotes: 3

GMB
GMB

Reputation: 222672

I think that you don't need the matching list syntax ([]). You can just list the search characters, separated with a pipe (|). Square brackets need backslash escaping.

select 1 
from dual where
    regexp_like(
      'Example [0,5%] item with special characters', 
      '\[|\]|°'
    );

Demo on DB Fiddlde

with t as (
    select 'Example [0,5%] item with special characters' v from dual
    union all select 'abc'  from dual
    union all select 'abc°' from dual
    union all select 'a[bc' from dual
    union all select 'abc]' from dual
)
select
    v,
    case when regexp_like(v, '\[|\]|°') then 'match' else 'no match' end match
from t

V                                           | MATCH   
:------------------------------------------ | :-------
Example [0,5%] item with special characters | match   
abc                                         | no match
abc°                                        | match   
a[bc                                        | match   
abc]                                        | match

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Focusing on your question, '\[' works:

select 1
from dual
where regexp_like('Example [0,5%] item with special characters', '\[');

Here is a db<>fiddle.

Upvotes: -1

Related Questions