Extract date from string in MySQL

Question

I have columns of string data formatted like this:

Katy Perry Tickets - Staples Center. 09/19/2014 7:30 PM. 209 11

Atlanta Braves Tickets vs Miami Marlins Tickets - Turner Field. 08/31/2014 5:10 PM. 411L 2

I need to extract the date value from each field, and the common format is looking at the first full stop from the right until the next full stop. The date is always between those two.

A full stop occassionally occurs in the text to the left of the date so we can't trust that.

How can I get that particular substring? SQL gurus, your advice needed...

Thanks.

Answer 1

Try using Substring_Index

Select  Substring_Index(Substring_Index( txt, ".",  -2), ".", 1)
From    Tablename

Answer 2

You can use the PREG functions for mysql , I have installed the UDF functions and I get this:

1- Creating and inserting sample data:

create table your_table (
    txt_col text
);

insert into your_table values  
('Katy Perry Tickets - Staples Center. 09/19/2014 7:30 PM. 209 11'),
('Atlanta Braves Tickets vs Miami Marlins Tickets - Turner Field. 08/31/2014 5:10 PM. 411L 2');

2- Testing the PREG_CAPTURE function using a pattern for dates:

mysql> SELECT PREG_CAPTURE(
           '/([0-9]+\\/[0-9]+\\/[0-9]+)\\s[0-9]+:[0-9]+\\s[a-z]+/i' , 
           txt_col, 0  ) as date_captured, 
    txt_col from your_table;
+--------------------+--------------------------------------------------------------------------------------------+
| date_captured      | txt_col                                                                                    |
+--------------------+--------------------------------------------------------------------------------------------+
| 09/19/2014 7:30 PM | Katy Perry Tickets - Staples Center. 09/19/2014 7:30 PM. 209 11                            |
| 08/31/2014 5:10 PM | Atlanta Braves Tickets vs Miami Marlins Tickets - Turner Field. 08/31/2014 5:10 PM. 411L 2 |
+--------------------+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

date_captured column is the extracted text.