Tableau Regex Extract date from file name

Question

I have a field that has the text file name being used as the data source. The file name is formatted like "file_name_example_2022-11-17_14.45.56.txt" with the "2022-11-17_14.45.56" being the date and time. I know I can do a series of RIGHT and LEFTs to extract the date time as a separate field, but I wanted to see if REGEX_EXTRACT would provide a cleaner way to do it. I've been looking at regular expression documentation and can't seem to figure it out. I am trying to end up with a full date time field.

So far I have tried

REGEXP_EXTRACT([File Paths], '\d(.+)')

and that results in "022-11-17_14.45.56.txt"

Answer 1

You can use

REGEXP_EXTRACT([File Paths], '\d{4}-\d{1,2}-\d{1,2}_\d{1,2}\.\d{1,2}\.\d{1,2}')

See the regex demo.

Details:

• \d{4}-\d{1,2}-\d{1,2} - four digits, -, one or two digits, -, one or two digits
• _ - a _ char
• \d{1,2}\.\d{1,2}\.\d{1,2} - one or two digits, ., one or two digits, ., one or two digits.