Reputation: 568
In Oracle PL/SQL code, using regular expressions, how can I remove all occurrences of certain characters, in any combination, from the beginning of a string?
For example, consider the characters '+', '-', '=', and ' ' (space) at the beginning of the following input:
' ++ -+= = - -= +ABCD EFG + XYD'
then the output must be:
'ABCD EFG + XYD'
A solution I'm thinking of is like:
1) set the starting point of the search at the beginning of the input (using ^)
2) set the ending point of the search just before the first character which is not any of the 4 characters of interest.
3) replace all spaces with empty character : REGEXP_REPLACE(' +', '')
4) replace all + with empty character
5) replace all - with empty character
6) replace all = with empty character
If this can be considered as a reasonable plan at all, I still don't know how item 2 above can be coded in regular expressions.
Thank you.
Upvotes: 0
Views: 91
Reputation: 3453
The regex ^[+\-= ]+
should work for you.
This should match 1 or more characters from the start of the line. If non of these character are at the start of the line then nothing will be replaced.
You should be able to use it like so SELECT REGEXP_REPLACE( <field>, "^[+\-= ]+", "" ) FROM <table>;
Upvotes: 2
Reputation: 67802
In 11g, you can use REGEXP_SUBSTR
with subexpressions:
SQL> SELECT regexp_substr(txt, '^([- +=]*)(.*)', 1, 1, '', '2') reg
2 FROM (SELECT ' ++ -+= = - -= +ABCD EFG + XYD' txt FROM dual);
REG
--------------
ABCD EFG + XYD
Upvotes: 1