GET DATE FROM MEMO/LONGTEXT ON MYSQL

Needed a way to get the date from a longtext (observation) column on MySQL without using regular expressions (Mysql 5.8), and I came up with this:

SET @DTDATA='WhiteSnake Tickets - Madson Square Garden. 15/12 7:30 PM. 209 11';

SELECT 
CASE 
WHEN (CHAR_LENGTH(@DTDATA) - CHAR_LENGTH(REPLACE (@DTDATA, '/', ''))) = 2 THEN STR_TO_DATE(SUBSTRING(@DTDATA, INSTR(@DTDATA,'/')-2,10), '%d/%m/%Y')
WHEN (CHAR_LENGTH(@DTDATA) - CHAR_LENGTH(REPLACE (@DTDATA, '/', ''))) = 1 THEN STR_TO_DATE(concat(SUBSTRING(@DTDATA, INSTR(@DTDATA,'/')-2,5),'/',YEAR(NOW())), '%d/%m/%Y')
ELSE
null
END ;


That will work with dd/mm/yyyy and dd/mm formats, and you can adapt to your needs changing the '%d/%m/%Y' format.


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06