SQL & PL/SQL

I have had a problem with backslashes in the replacement text of the REGEXP_REPLACE function.

You see, I'm using REGEXP_REPLACE to implement a "replace whole words only" function. The implementation consists of four find-replace pairs constructed as follows (with the <find> and <replace> taking the place of the actual strings):

update X set Y = REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE ('([^[:alnum:]])<find>([^[:alnum:]])', '\1<replace>\2'), '^<find>([^[:alnum:]])', '<replace>\1'), '([^[:alnum:]])<find>$', '\1<replace>'), '^<find>$', '<replace>')

The problem comes in when <replace> contains backslashes. According to the documentation (the Oracle Database SQL Reference, section 7) suggests that \ must appear as \\ in the replacement string.

However, I'm finding that you use \\ to represent \ in the replacement only if the find string does not contain groups (the parenthesized expressions). Is that how it's supposed to work? We're using 10g.

Here is a small SQL script to illustrate the behaviour I'm seeing. It is supposed to replace "is" with "\". I expected the expression for OUTPUT1 to be correct, but it seems that the expression for OUTPUT2 works (notice that the second expression is different in only ONE CHARACTER from the first).

create table REGEXP_TEST ( INPUT VARCHAR2(15), OUTPUT1 VARCHAR2(15), OUTPUT2 VARCHAR2(15), EXPECTED VARCHAR2(15), INCORRECT VARCHAR2(15)) ;

insert into REGEXP_TEST (INPUT, EXPECTED, INCORRECT) values ('This is a test', 'This \ a test', 'This \\ a test');

insert into REGEXP_TEST (INPUT, EXPECTED, INCORRECT) values ('This is', 'This \', 'This \\');

insert into REGEXP_TEST (INPUT, EXPECTED, INCORRECT) values ('is a test', '\ a test', '\\ a test');

insert into REGEXP_TEST (INPUT, EXPECTED, INCORRECT) values ('is', '\', '\\');

update REGEXP_TEST set OUTPUT1 = REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE (INPUT, '([^[:alnum:]])is([^[:alnum:]])', '\1\\\2'), '^is([^[:alnum:]])', '\\\1'), '([^[:alnum:]])is$', '\1\\'), '^is$', '\\');

update REGEXP_TEST set OUTPUT2 = REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_REPLACE (INPUT, '([^[:alnum:]])is([^[:alnum:]])', '\1\\\2'), '^is([^[:alnum:]])', '\\\1'), '([^[:alnum:]])is$', '\1\\'), '^is$', '\');

select * from REGEXP_TEST;