Removing all special characters using REGEXP_REPLACE in oracle

Question

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;

Wernfried Domscheit · Accepted Answer · 2020-01-08 14:18:10Z

1

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.

answered Jan 8, 2020 at 14:18

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2020-01-08 12:18:29Z

0

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;

answered Jan 8, 2020 at 12:18

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Wernfried Domscheit Over a year ago

I don't think collation is the problem here, TO likes to escape the single quote.

Collectives™ on Stack Overflow

Removing all special characters using REGEXP_REPLACE in oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related