Parsing out a date

[JohnMaty]

HI Everyone,

I have a field on a table that I am trying to parse out a date from within a large varchar field.  Here is an example of the field (OffenseConditionTxt):

"Defendant has permission to participate in and complete the DUI ALTERNATIVE TO JAIL program within 90 days of this date. The defendant must pay the DUI Alternative program cost within 60 days of this sentencing date in order to participate in that program.  During the DUI Alternative program the defendant is required to complete all DUI conditions as determined by the Court and participate in all other components of the program and follow all rules of the program and all directives of the program administrators. If the Defendant successfully completes the DUI Alternative to Jail Program prior to the date of compliance for his jail sentence, this court will accept that successful completion of the DUI Alternative to Jail Program and grant the Defendant credit for that program.  If the Defendant documents successful completion of that program before, or on, his compliance date the Defendant will be credited for time served.

If the defendant fails to complete the DUI ALT to Jail, the defendant must comply w/his jail sentence on 12/8/17"

I am looking for the two fields listed in bold.  How do I do this with RegEx?  Note that it may not always be at the end of the field.

[JohnJPS · Accepted solution]

You could try something like a Formula with a new field "parseComplyDate" and...

IF REGEX_Match([fld1], ".*\<comply\>.*[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}.*") < 0 THEN
   REGEX_Replace([fld1], ".*\<comply\>.*([0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}).*", "$1")
ELSEIF REGEX_Match([fld1], ".*[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}.*\<comply\>.*") < 0 THEN
   REGEX_Replace([fld1], ".*([0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}).*\<comply\>.*", "$1")
ELSE
   Null()
ENDIF

... basically this allows comply to be before or after the date; if it finds it either way, it will return the date... if it doesn't find a match, it will return null.

[JohnMaty]

Worked great.  At Inspire this year I took the parsing class and figured there was a way to do it.  Just right now I am too lowly of a padawan to get my RegEx to work.  I appreciate the assist.