Extraction of a substring adapted to Regex and delimited by spaces

advertisements

Trying to parse dates entered in various ways and contexts, and that may or may not be present in a given record

I can SELECT candidate rows with

SELECT * FROM table WHERE column REGEXP '[-|.|/][0-9][0-9][-|.|/]' ;

This will indeed select records that read something like

I was on top of mount Everest (2010-10-10)
i went to see the doctor on 13/12/10 and she told me I was in great shape.

where the matched values are -10- and /12/ for the first and second records respectively.

Now, I want to extract the date from the column. Not merely the -10- or /12/ but the full date fragments 2010-10-10 or 13/12/10, i.e. the matched expression expanded backwards up to a space or a parenthesis, and expanded forward at as space of parenthesis.

Sorry if this is obvious - I am not familiar with REGEX.


you will have to find a pattern for the date input. you can use regex in your where, but you will need to isolate it somehow. is it always the last part of the col?

now that you isolated the location, you can do a case style select

select case when right(date,4) between 1900 and 2200 then right(date,10) #mm/dd/yyyy when left(date,4) between 1900 and 2200 then concantenate(left(right(date,5),2), "/", right(date,2)) end as date

that kind of ordeal

EDIT;;

SET @fieldName = "I was ON top of mount Everest (2010-10-22)";

SELECT IF( STR_TO_DATE( CONCAT ( RIGHT(SUBSTRING_INDEX(@fieldName,"-",1),4), "-",RIGHT(SUBSTRING_INDEX(@fieldName,"-",2),2), "-",LEFT(SUBSTRING_INDEX(@fieldName,"-",-1),2) ), '%Y-%m-%d' ) IS NULL ,

"bad date",

"good date")

but now for bad date and good date, you keep chaining that style to loop through all variants of dates...

although the best solution is to make that date a diff col in a special format if you can as it is entered