REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

Database
Fan

REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

Hello Teradata forum users,

I'm on Teradata database version 14.00.07.15 and I want to use the REGEXP_REPLACE function on a varchar field, to replace everything that is not a digit (0-9):

     select 'abcde1234f5' as vat,REGEXP_REPLACE(VAT,'[^0-9]','',1,0,'I')AS VAT_CLEAN

This returns a correct answer:

     abcde1234f5  12345

But, if I execute the same statement on a slightly modified value of "vat" field (added one more non-digit char 'x'):

    select 'abcde12x34f5' as vat, REGEXP_REPLACE(VAT,'[^0-9]','',1,0,'I') AS VAT_CLEAN1

It returns this:

    abcde12x34f5   1234f5

The replacement is incomplete... It seems that only the first 6 matches are processed! Though, I use '0' as value for the argument occurence_arg, which means that every matching string should be replace.

Is this a bug?

Thanks a lot for your advices!

Gordh

3 REPLIES
Junior Contributor

Re: REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

Hi Gordh,

it's working as expected on a 15.00.03.03, you should open an incident.

As a workaround you can use an old trick:

oTranslate(VAT, oTranslate(VAT, '0123456789',''), '')

The nested oTranslate returns all non-digits characters in VAT to be removed in the next step.

Fan

Re: REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

Thanks a lot!

Gordh

Junior Supporter

Re: REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

This works though

select 'abcde12x34f5' as vat, REGEXP_REPLACE(VAT,'[^0-9]+','',1,0,'I')