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:
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:
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!
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.
This works though
select 'abcde12x34f5' as vat, REGEXP_REPLACE(VAT,'[^0-9]+','',1,0,'I')