Thinking Out Loud

January 28, 2021

Remove Characters From String Using regexp_replace

Filed under: 12.2,regexp — mdinh @ 1:38 pm

It’s probably have been half a century since I have coded PL/SQL.

So there I was, reviewing PL/SQL code and it looks rather redundant.

Did you know there is defined variable USER for PL/SQL code and using sys_context is not necessary?

Also, regexp_replace can be used to remove all characters from string.

Here is an example:

Basically, one line of code replaces all the redundant code.

I have no idea why there is a need to extract characters.

DINH099PD@ORCLPDB1 > show user
DINH099PD@ORCLPDB1 > @dinh.sql
  2    -- Remove character from l_user
  3    l_user VARCHAR2(30) := regexp_replace(USER, '[[:alpha:]]|_');
  4    l_user2 varchar2(20);
  5    l_output varchar2(100);
  6  BEGIN
  7    -- Remove character from l_user2
  8    l_user2 := sys_context('USERENV', 'CURRENT_USER');
  9    l_output := replace(replace(l_user2, 'DINH', ''),'PD', '');
 10    dbms_output.put_line (l_user);
 11    dbms_output.put_line (l_output);
 12  END;
 13  /

PL/SQL procedure successfully completed.


Blog at