1

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"

crazydiamond
  • 25
  • 4

1 Answers1

2

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.
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563