why cannot match char of ']' with regexp_like in oracle?

Question

When I input these SQLs, they return result 1:

sql 1:

select 1 from dual where regexp_like('R[23','^[[0-9a_zA-Z]+$');

returns 1

sql 2:

select 1 from dual where regexp_like('[]','^\\[\\]$');

returns 1

But when I input this SQL, it cannot return result with 1

sql 3:

select 1 from dual where regexp_like('R23]','^[0-9a_zA-Z\\]]+$');

returns null

**SQL 2** does not output `1`. You want `select 1 from dual where regexp_like('[]','^\[]$');` with only a single `\ ` escape character to prevent the bracket expression from starting. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=46b1c0f9ce71e5111aa801c750571d44). — MT0, May 12 '20 at 08:58
[Here](https://stackoverflow.com/a/19420806/3832970) is the answer. — Wiktor Stribiżew, May 12 '20 at 09:42

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Put the closing square bracket first in the regular expression and do not escape it:

SELECT 1
FROM   dual
WHERE  regexp_like( 'R23]', '^[]0-9a-zA-Z]+$');

You also want a-z rather than a_z.

From the Oracle documentation:

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.

Your expression (after correcting _ to -):

SELECT  1
FROM    DUAL
WHERE   regexp_like('R23]','^[0-9a-zA-Z\\]]+$');

Is matching to see if there is a character which matches one of the characters 0123456789abcdefghiklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ\\ at the start of the string (there is no escape character in the regular expression so \ is treated as part of the character group to match) and then is followed by one-or-more ] characters and then the end of the string.

So:

SELECT  1
FROM    DUAL
WHERE   regexp_like('\]]]]]]]]]]','^[0-9a-zA-Z\\]]+$');

Outputs 1

db<>fiddle

why cannot match char of ']' with regexp_like in oracle?

1 Answers1