Replace special characters in Oracle database

Question

I have more than 200 000 data in a table with a column that has a special character 'Æ??` and text is test1234Æ??. How to replace that with '?' symbol?

Answer 1

The Æ has unicode 00C6. Just google "Æ unicode" to find that - with that knowledge, you can then use the UNISTR function to represent that character. But you can also just paste that character in your sql as shown below if your client supports the unicode characters.

WITH mystrings AS
(SELECT 'found a Æ in this text' as str FROM DUAL UNION ALL
 SELECT 'text is test1234Æ??' FROM DUAL
)
SELECT 
  REPLACE(str,UNISTR('\00c6'),'?') as clean_string,
  REPLACE(str,'Æ','?') as clean_string2,
  REPLACE(str,UNISTR('\00c6??'),'?') as clean_stringnoqm
  FROM mystrings;

CLEAN_STRING            CLEAN_STRING2           CLEAN_STRINGNOQM       
----------------------- ----------------------- -----------------------
found a ? in this text  found a ? in this text  found a Æ in this text 
text is test1234???     text is test1234???     text is test1234?   

If you want to only keep characters in the range a-zA-Z and comma you could use a regular expression. There are plenty of other answers around for that, for example this one.

WITH mystrings AS 
( SELECT
  'Brand1® is supercool, but I prefer bRand2™ since it supports Æ' AS str
    FROM dual
)
SELECT
  regexp_replace(str,'[^A-Za-z0-9, ]', '?') AS clean_string
  FROM mystrings;