Using RegEx to Extract Date

MycahD
MycahD Member
in Magic ETL

Hi all!

I am looking for advice on extracting a Maturity Date from a column containing a string of text, example:

string: 12months,MATURITY DATE:23-Jun-2022,BALANCE;$12345,PRODUCT

Return: 23-Jun-2022

I am able to successfully select the date by using: \d{2}-[A-z]{3}-\d{4}

But am unable to replace this so that it only returns the date. I always end up just getting the full text string.

Thanks in advance!

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Try this:

    REGEXP_REPLACE(`string`, '^.*(\d{2}-[A-z]{3}-\d{4}).*$', '$1')


    You need to replace the entire string not just the substring. This is why there's ^.* and .*$ to match anything before and after the date value respectively.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Try this:

    REGEXP_REPLACE(`string`, '^.*(\d{2}-[A-z]{3}-\d{4}).*$', '$1')


    You need to replace the entire string not just the substring. This is why there's ^.* and .*$ to match anything before and after the date value respectively.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MycahD
    MycahD Member

    Thank you @GrantSmith, worked perfectly. I appreciate it!

Categories