I hve a column having date as 30-Jun-20. this column has different date formats. I need to check if the date given is in the format listed below:
The valid values are the following: 'DDMMYYYY' , 'MMDDYYYY' , 'YYYYMMDD' , 'DD-MM-YYYY' , 'MM-DD-YYYY' , 'YYYY-MM-DD' , 'DD.MM.YYYY' , 'MM.DD.YYYY' , 'YYYY.MM.DD' , 'DD/MM/YYYY' , 'MM/DD/YYYY' , 'YYYY/MM/DD' , 'YYYY-MM-DDThh:mm:ss.000000000' , if not than flag as "Not Valid" else "Valid" |
Please help.
Solved! Go to Solution.
I'll break it down for you:
^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$
^ = Start of Line
(19|20) = () defines a GROUP and it says find either '19' or '20'
\d\d = find two digits - 0-9
[-/.] = then find - one of these from the set '-' or '/' or '.' where [ ] defines the set
(0[1-9]|1[012]) = GROUP find first a '0' then find 1 thru 9 OR (| = OR) find first a 1 then find 0 or 1 or 2
[-/.] = find either a dash '-' or '/' or '.'
(0[1-9]|[12][0-9]|3[01]) = now look for the last set of valid numbers sets - ( ) means this is a GROUP and 0[1-9] OR (the | means OR) [12] means find a 1 or 2 then [0-9] means find 0 thru 9, then another OR (| means OR) next find a 3 followed by either a 0 or 1
$ = end of string
Basically the first group checks for either 19xx or 20xx years ONLY
Then it looks for the two digits
Then it looks for choices of seperaters - dash dot or slash
Then it looks for valid date ranges for months - so a 30 would not match since there is no 30th month.
Then it looks for choices of seperaters - dash dot or slash
Last it looks for valid day of the month ranges.
Look at it like this:
^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$
^ = start of string
(19|20)\d\d = match on start of 19 or 20, followed by two more digits
[- /.] = match on either a dash a slash or a dot
(0[1-9]|1[012]) = next match on a first digit of 0 followed by 1-9, OR (| = OR) match on a first digit of 1 followed by either a 0 a 1 or a 2 (so a 3 or greater will not match and you only get valid dates)
[- /.] = match on either a dash a slash or a dot
(0[1-9]|[12][0-9]|3[01]) = look for 3 valid combos (note the pipes | dividing the three choices) first 0 followed by either 1 thru 9 OR 1 or 2 followed by 0-9 OR starts with 3 and is followed by either 0 or 1
$ = end of string.
Brilliant!!!... I always get problem with RegEX..... you have explained it so well now I will try by myself some string and number combinations...... Many thanks for the help.
Sorry for asking so many questions...... This is fine perfectly with Normal Formula tool but If I',m trying to do same with IN-DB Formula tool , it is not working. so Is this only work with simple Alteryx tool How can I do the same when fetching data from DB
I have tried as below:
If REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$)
THEN 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d$)
then 0
ElseIF REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])(19|20)\d\d$)
THEN 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])$)
Then 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d$)
Then 0
Elseif REGEX_Match("OFFER_EXPIRY_DATE", ^(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])(19|20)\d\d$)
Then 0
ElseIF REGEX_Match("OFFER_EXPIRY_DATE", ^(19|20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|[[12][0-9]|3[01])T(0[0-9]|1[0-9]|2[0123]??):(0[0-9]|[12345][0-9]):(0[0-9]|[12345][0-9]).\d+$)
Then 0
Else 1
Endif
I do not think this will work IN-DB because at that point you need to write the SQL (T-SQL or P-SQL or ? SQL) and how SQL does RegEX is a different flavor of REGEX.
Alteryx REGEX is base don PERL code - and I assume your data base does not run PERL code.
Could you pull the data into an Alteryx workflow, run the REGEX and then put the results set back in the DB.
Yep... got it... I have now pulled the data into Alteryx and now have worked using Formula tool. Really appreciate the efforts and swift response from Alteryx community..... A month back I started learning and exploring Alteryx which initially was a nightmare for me but with the support I have got from the community, really have helped me to learn alot.
THANKYOU 🙂