Teradata : Extract Date from string

Question

There is a notation field with different number of characters. Eg

Exceed Limit Fee 26-January-2020

or

Account Exceed Limit Fee 05-June-2001

I want to extract date part of it and convert that to date type. After that compare it with date field of other table.

How can I achieve this in Teradata?

Answer 1

You can extract the date using a RegEx and then cast to a date:

to_date(regexp_substr(x, '\d{1,2}-\w{3,9}-\d{4}'), 'dd-month-yyyy')

This is s basic regex checking for two-digit day a string and a -digit year. You could enhance it to match a correct month name, 'January', etc., just search for a PCRE2 compatible RegEx.