How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

Database

How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

Given a string "select * from test_1 where dt = '2015-01-01' or dt = '2015-01-02'", I would like to replace all numeric values with 0 but would like to leave test_1 as test_1 only. How do I write such a regexp_replace()?

4 REPLIES
Junior Contributor

Re: How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

This finds all digits not preceeded by an underscore:

regexp_replace(col, '((?<=[^_])[0-9]+)','0')

Re: How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

Thanks. Appreciate.

Re: How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

Hi Dieter,

Could you please explain ((?<=[^_])[0-9]+) this part in solution provided by you elaborately?

Junior Contributor

Re: How to write a Teradata regexp_replace() that replaces all numeric values but _N where N is a numeric value?

?<= -> this is a lookbehind, i.e. check if this expression [^_] (any character but no underscore) is before the next group, but don't add it to the match. 

[0-9]+ -> any number of digits