Skip to content
Advertisement

How to remove one or more instances of ‘?*’ in a string using REGEXP_REPLACE function in oracle?

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

Demo

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement