Search code examples
sqloracle-databaseregexp-replace

how not to replace "]" when using regex_replace for removing special characters

I'm trying to remove few special characters from a comment column in my table. I used the below statement but it seems to remove the ']' even though it is in the ^[not] list.

UPDATE TEST 
set comments=REGEXP_REPLACE(
               comments,
               '[^[a-z,A-Z,0-9,[:space:],''&'','':'',''/'',''.'',''?'',''!'','']'']]*',
               ' '
               );

The table data contains the following:

[SYSTEM]:Do you have it in stock? 😊

My requirement is to have:

[SYSTEM]:Do you have it in stock?
Solution

  • My try, I just removed the commas, put the "accepted" characters after the initial "not"(no brackets). A special case are the brackets: https://dba.stackexchange.com/a/109294/6228

    select REGEXP_REPLACE(
         '[ION] are varză murată.',
         '[^][a-zA-Z0-9[:space:]&:/,.?!]+',
         ' ') 
from dual;

Result:
[ION] are varz  murat .