Multiple conditions in Regex_Replace

Question

I want to replace special characters from two different words as shown in the image below. From the first word, I want to replace a special character with "I" and from the second word, I want to replace a special character with "U". My query is like below: It works for the first word. Can you pls assist?

SELECT 
   distinct ABC REGEXP_REPLACE(REGEXP_REPLACE(ABC, r'([^\p{ASCII}]+)', 'I') ,r'\&', 'U') 
FROM Table 
where ABC like '%B??RD%' or ABC like '%M??D%'; 

Answer 1

Instead of using REGEX, you can use CASE for this scenario:

WITH CTE as (
SELECT  'B??RD'as ABC,
UNION ALL SELECT 'M??D'as ABC)

SELECT 
   ABC,
   CASE ABC
    WHEN 'B??RD' THEN 'BIRD'
    WHEN 'M??D' THEN 'MUD'
   END AS output

FROM CTE

Output: