CODEWITHSUNDEEP
regexplsqloracle11g
Zhe Xin
Zhe Xin

Reputation: 213

Ask for a reqular expression to remove prefix or suffix from a string in Oracle

I want to remove prefix or suffix from a string, the string looked like below:
L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z| or
|L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z|||
I want to remove delimiter | in the end or beginning of the string and in the final, the string should be looked like this:
L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z

So, how to write a regular expression to do this in PLSQL?

Upvotes: 0

Views: 1613

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

As EatÅPeach suggested, you don't need to use a regular expression for this, the built-in TRIM function will do:

select trim('|' from '|L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z|||')
from dual;

TRIM('|'FROM'|L-CHGSUP-CDO-BR-NET|MSCOORDINATORX|MSCOORDINATORZ|||')
--------------------------------------------------------------------
L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z

Or if you already have the value and want to trim it within PL/SQL (as a contrived example):

set serveroutput on
declare
  old_value varchar2(80);
  new_value varchar2(80);
begin
  old_value := '|L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z|||';
  new_value := trim('|' from old_value);
  dbms_output.put_line(new_value);
end;
/

anonymous block completed
L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z

Upvotes: 1

pablomatico
pablomatico

Reputation: 2242

This regular expression does the job:

select regexp_replace('|L-CHGSUP-CDO-BR-NET|MS COORDINATOR X|MS COORDINATOR Z|||','(^[\|]*)|[\|]*$') from dual

Upvotes: 1

Related Questions