CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle12c
sohail ansari
sohail ansari

Reputation: 11

Removing all special characters using REGEXP_REPLACE in oracle

I am able to remove all sepecial charaters as below:

SELECT REGEXP_REPLACE('91D5 ZBA DISCONNECTION  https://mail.com/owa/#path=/mailÂ]*Debit Bank Ctry next Business Day is 06-Jan-2020', '[^0-9A-Za-z]', ' ')  
FROM dual;

Output : 

91D5 ZBA DISCONNECTION  https   mail tcs com owa  path  mail   Debit Bank Ctry next Business Day is 06 Jan 2020

However if there is any single inverted comma inside my description as below if fails how do I escape single inverted comma sequence using REGEXP_REPLACE function:

SELECT REGEXP_REPLACE('91D5 ZBA DISCONNECTION  https://mail.com/owa/#path=/''mailÂ]'*Debit Bank Ctry next Business Day is 06-Jan-2020', '[^0-9A-Za-z]', ' ')  
FROM dual;

Upvotes: 1

Views: 1229

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Use Text Literal, e.g. 

SELECT REGEXP_REPLACE(q'#91D5 ZBA DISCONNECTION  https://mail.com/owa/#path=/''mailÂ]'*Debit Bank Ctry next Business Day is 06-Jan-2020#', '[^0-9A-Za-z]', ' ')  
FROM dual;

quote_delimiter is any single- or multibyte character except space, tab, and return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiterappears in the text literal itself, ensure that it is not immediately followed by a single quotation mark.

If the opening quote_delimiteris one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I suggest that the reason the  character is not being replaced is because the particular collation you are using treats  and A as being the same character. One possible workaround here would be to force a collation which distinguishes between the two characters when you query:

SELECT
    REGEXP_REPLACE(val, '[^0-9A-Za-z]', ' ')
FROM yourTable
COLLATE utf8_unicode_ci;

Upvotes: 0

Related Questions