asked on

Oracle regex_replace negative lookbehind alternative

I'm using the following regex expression in c#.NET
(?<![0-9])0+
It will remove leading zeros from each block of digits found and works just fine in c#.

Now, I need to do the same in Oracle sql. However, Oracle is not supporting negative lookbehind (<!)....
So, I need an alternative for
select REGEXP_REPLACE('00AB00010', '(?<![0-9])0+') text from dual
The expected result in the above example is AB10.

slightwv (䄆 Netminder)

I'm not 100% sure this will replace a negative lookbehind since I don't deal with those in Oracle but this produces the results based on that example:
select REGEXP_REPLACE('00AB00010', '0+([^0]+)','\1') text from dual;

IVL

ASKER

With the example it works, however, for another example it doesn't :
select REGEXP_REPLACE('00AB00010CD', '0+([^0]+)','\1') text from dual;
If my understanding is correct, it searches for blocks of characters not containing "0", and removes any zeros in front of it.
As a result this second example wile resolve as "AB1CD" while it should be "AB10CD"

IVL

ASKER

Another example "00AB00010CD002500" should result in "AB10CD2500"

IVL

ASKER

I think I found a solution combining Ltrim with the regex_replace function :
select ltrim(REGEXP_REPLACE('00ASDEB0001000CD004500', '*?([^0-9]+)0+','\1'), '0') text from dual;
returns 'ASDEB1000CD4500' which is correct.
The regex is removing any zero behind each block of characters different from 0123456789, the ltrim is removing potentional zeros at the beginning of the string.

If anyone has a better solution or a regex doing this without the extra ltrim, let me know.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>I think I found a solution combining Ltrim with the regex_replace function :

I suppose I should have refreshed sooner?

The LTRIM should be a quick call so I wouldn't worry about it.

My last offering uses nested parans and that is even more expensive as far as calls go. So, if the LTRIM works, I would go with that!

Let me play with your solution to see if I can remove the LTRIM.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IVL

ASKER

Thank for the assistance.