Other day we saw how to extract numbers from a string lets spice the things up let’s see how we can extract Dates from a string.

At first we will see how extract a string in the Format of

MM/M(any character)DD/D(any Character)YYYY/YY OR
DD/D(any character)MM/M(any character)YYYY/YY

Sample Data

FormatText String
MM-DD-YYYYI born on 12-09-1994
MM.DD.YYYYI born on 12.09.1994
MM/DD/YYYYI born on 12/09/1994
M/D/YYYYI Born on 1/1/1994
M/D/YYI Born on 1/1/94
M-D/YYYYI Born on 1-9/1994
MM?DD?YYYYI Born on 12/09/1994
M-DD-YYI Born on 1-09/94
DD-MM-YYI Born on 09-12-1994

Calculation at Tableau Level

Pattern : (\d{1,2}.\d{1,2}.\d{2,4})

Final O/P:

Lets Extract the dates which are

Month Name(any Character)DD/D(any Character)YYYY/YY

MonthJanuary OR JAN or jan or january or JANUARY
Day9 OR 09
year2019 OR 19

Sample Data:

FormatText String
MMMM/DD/YYYYI Born on September/12/1994
MMM/DD/YYYYI born on Sep/12/1994
MMM/D/YYI Born on SEP/9/94

Tableau Calculation:

REGEXP_EXTRACT(LOWER([Text String]),
‘((?:jan(?:uary)?|feb(?:ruary)?|mar(?:ch)?|apr(?:il)?|may|jul(?:y)?|aug(?:ust)?|sep(?:tember)?|nov(?:ember)?|dec(?:ember)).\d{1,2}.\d{2,4})’)

Output:

As if you see we used lower for the text string to match the pattern for lower case if you want you can use UPPER and change your Pattern to below

REGEXP_EXTRACT(UPPER([Text String]),
‘((?:JAN(?:UARY)?|FEB(?:RUARY)?|MAR(?:CH)?|APR(?:IL)?|MAY|JUL(?:Y)?|AUG(?:UST)?|SEP(?:TEMBER)?|NOV(?:EMBER)?|DEC(?:EMBER)).\d{1,2}.\d{2,4})’)

WEEKDAY :Lets try to extract Weekday from string

Sample Data:

Text Format
I born on September,12 2019 which is on Sunday

Tableau Calculation:

REGEXP_EXTRACT(LOWER([Text Format]),
‘((?:sun(?:day)?|mon(?:day)?|tue(?:sday)?|wed(?:nesday)?|thu(?:rsday)?|fri(?:day)?|sat(?:urday)?))’)

O/P:

Time Extract: Either 24 Hr or AM/PM

Sample Data:

Time
I Born on 10:12:12 AM
I born at 1:56:00 PM
I Born at 13:12:59 am

Tableau Calculation:

REGEXP_EXTRACT(LOWER([Time]),
‘((?:(?:(?:0?[1-9]|1[0-2])(?::|.)[0-5]\d(?:(?::|.)[0-5]\d)? ?[aApP][mM])|(?:(?:0?\d|1\d|2[0-3])(?::|.)[0-5]\d(?:(?::|.)[0-5]\d)?)))’)

O/P:


References:

  1. rgxdb
  2. rgexr

Hope you enjoyed this blogspot. Do Subscribe by entering your email in the footer to get the new content