I’m getting the characters '?*' one to three times in a column called Line. I am required to remove these characters. How do I do it using Replace or REGEXP_REPLACE?
SELECT
Line, REGEXP_REPLACE(LINE,'[?*]','')--([^+]+)
FROM
TABLE
WHERE
INSTR(LINE,'?*') != 0;
where
REGEXP_REPLACE(LINE,'?*','') replaces the ? alone and leaves the * untouched.
REGEXP_REPLACE(LINE,'?*','') replaces nothing.
REGEXP_REPLACE(LINE,'[?*]','') replaces all ?s and all *s. I am only replacing when ? and * comes together as ?*.
Advertisement
Answer
Use (?*) as pattern :
with tab(line) as ( select 'abc?*ghh*?g?l*' from dual union all select '?*U?KJ*H' from dual union all select '*R5?4*&t?*frg?*' from dual ) select regexp_replace(line,'(?*)','') as "Result String" from tab; Result String ------------- abcghh*?g?l* U?KJ*H *R5?4*&tfrg