code_error code_error - 6 months ago 9
SQL Question

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?

Answer

My try, I just removed the commas, put the "accepted" characters after the initial "not" and escaped your non alphabetic characters with \. A special case are the brackets: http://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 .
Comments