CODEWITHSUNDEEP
regexplsql
Majix
Majix

Reputation: 568

usnig regular expressions, how any combination of certain characters can be eliminated from the beginning of a string

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

Answers (2)

AeroX
AeroX

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

Vincent Malgrat
Vincent Malgrat

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

Related Questions