All Forums Database
gordh 2 posts Joined 07/15
29 Jul 2015
REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0

Hello Teradata forum users,
I'm on Teradata database version 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!

dnoeth 4628 posts Joined 11/04
29 Jul 2015

Hi Gordh,
it's working as expected on a, 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.


gordh 2 posts Joined 07/15
29 Jul 2015

Thanks a lot!

nealvenna 9 posts Joined 10/12
31 Jul 2015

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

You must sign in to leave a comment.