CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
code_error
code_error

Reputation: 19

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?

Upvotes: 0

Views: 668

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

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 .

Upvotes: 0

CodeFuller
CodeFuller

Reputation: 31312

You have two mistakes in you regex:

  1. Do not put characters in quotes and don't split them with comma.
  2. Remove inner square brackets.

And place closing square brackets first in the list, just after initial circumflex. Fixed regex:

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

Upvotes: 1

Related Questions