INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1]   Go Down

Author Topic: Date in Caption field, Regex help  (Read 1145 times)

Daydream

  • Citizen of the Universe
  • *****
  • Posts: 771
Date in Caption field, Regex help
« on: January 03, 2012, 05:18:14 am »

I can understand and work with Regex but I'm no expert.

I'm targeting date values in the [Caption] field. Dates are in two formats, fully spelled -> February 28, 2006 and abbreviated -> Sept. 21, 2003. Months can be either small or upper case.
I need them returned as yyyy-mm-dd in a separate field; the Caption field contains any kind of characters. How do I do it? Word boundaries? Appreciate any help.
Logged

MrC

  • Citizen of the Universe
  • *****
  • Posts: 10462
  • Your life is short. Give me your money.
Re: Date in Caption field, Regex help
« Reply #1 on: January 03, 2012, 01:47:07 pm »

Here's the RE to grab what appears to be a date in your Caption field.

   Regex([Caption], /#((?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\S*\s\d{1,2},\s*\d{4})#/, 1)

You can create an expression column to see that it works for you.

This grabs the date into one sequence.  To format the date, we can't use this sequence directly, since you want to convert from month names into numbers, and MC's FormatDate() function uses internal date representations, not strings.  So, we can assign the string to a new date field, where MC does understand and convert human-readable date forms, and then you can use that value inside FormatDate().

Create a new field, of type Date, lets call it "Date (Caption)".

Select your files, and use the RE above to pull the value from Caption, and place it into [Date (Caption)].  In the Tag Action Widow, or a column, select-to-edit the Date (Caption) field and enter the assignment expression:

   =Regex([Caption], /#((?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\S*\s\d{1,2},\s*\d{4})#/, 1)

Now, you can use the expression:

   formatdate([Date (Caption),0], yyyy-MM-dd)

wherever you want.
Logged
The opinions I express represent my own folly.

vagskal

  • Citizen of the Universe
  • *****
  • Posts: 1227
Re: Date in Caption field, Regex help
« Reply #2 on: January 04, 2012, 11:30:38 am »

Elegant, MrC!

A new data type &datatype=[date] would of course simplify this.
Logged

Daydream

  • Citizen of the Universe
  • *****
  • Posts: 771
Re: Date in Caption field, Regex help
« Reply #3 on: January 04, 2012, 04:12:53 pm »

Yeap, worked like a charm! Big thanks!
Logged
Pages: [1]   Go Up