Find differently styled date in comments

Question

I have a another question for preg_match.

I have a table, where the date of the comment is written inside the comment itself ( by hand ) and now I need to extract that date and place in a different column.

I found out, that the comments and dates are styled like this:

+-------+----------------------+--------------+
| id    | warning_sent         | warning_date |
+-------+----------------------+--------------+
|  6109 | 2011-06-28           | 0000-00-00   |
|  6123 | 2012 02 14           | 0000-00-00   |
|  6126 | Some text 18.11.10\n | 0000-00-00   | /* This is 2010-11-18 */
| 13750 | 2011-06-28\n         | 0000-00-00   |
|  8108 | 7-01-2010            | 0000-00-00   |
|  9954 | 2012.07.03           | 0000-00-00   |
|  6110 | Some text 21-02-2011 | 0000-00-00   |
+-------+----------------------+--------------+

Now, the only thing, I could think of ( and know how to do ), is looking for the first number, then for the last. And somehow analyzing the string in between, but this comes to a serious problem if the comment text contains a number on its own.

So the question is: How can I find all the dates ( there are only one in each comment ) inside the comments and store them automatically inside the other column in my desired YYYY-MM-DD format?

Edit: This worked, thanx @infinity

$query = "SELECT id, warning_sent FROM data";
$result = qq( $query );

foreach( $result as $values )
{
    preg_match( '/[\d]{4}((-|\s|\.)[\d]{2}){2}|([\d]{1,2}(\.|-)){2}[\d]{2,4}/', $values['warning_sent'], $matches );

    $old_date = $matches[0];
    $new_date = '0000-00-00';

    /* 2011-06-28, 2012 02 14, 18.11.10, 7-01-2010, 2012.07.03, 21-02-2011, 20.10.2010 */
    $formattings = array( 'Y-m-d', 'Y m d', 'd.m.Y', 'd.m.y', 'j-m-Y', 'Y.m.d', 'd-m-Y' );

    for( $k=0; $k<sizeof( $formattings ); $k++ )
    {
        $sub_date = DateTime::createFromFormat( $formattings[$k], $old_date );
        $sub_date = (array)$sub_date;
        $timestamp = strtotime( $sub_date['date'] );
        $check_date = date( $formattings[$k], $timestamp );
        if( $old_date == $check_date ) $new_date = date( 'Y-m-d', $timestamp );
    }

    $warning_sent_date[$values['id']] = $new_date;

}

@infinity As you can see, there are 3 different formats of the dates used. — Peon, Nov 12 '12 at 12:53
@DainisAbols do the dates have a exact position? (i.e. at the end, at the begining ect.?) — Sid, Nov 12 '12 at 12:56
@Sidath As you can see, **no**. The comments are written by hand. It varies how/where the operator decided to enter the date in the comment. — Peon, Nov 12 '12 at 12:58
@DainisAbols Just a suggestion. you can always add a seperate input field to get date from the user input. quicker. better. easier — Sid, Nov 12 '12 at 13:18
@Sidath This is a fairly old db and as you can see from my example, I intend to put all the dates in the new column in the future. I just need this, for the old entries. — Peon, Nov 12 '12 at 13:26

Alex Rashkov · Accepted Answer · 2012-11-12T13:11:52.823

2

If you have just these 3 formats that should work

'/[\d]{4}((-|\s|\.)[\d]{2}){2}|([\d]{1,2}(\.|-)){2}[\d]{2,4}/'

Updates RegEx

http://regexr.com?32pbf

edited Nov 12 '12 at 13:11

answered Nov 12 '12 at 12:58

Alex Rashkov

9,833
3
32
58

works just fine. I update the question with my final code, if interested. – Peon Nov 12 '12 at 14:14

score 0 · Answer 2 · answered Nov 12 '12 at 13:04

You can use preg_match to find rows with dates embedded in the comments. Others have presented ways of doing that.

However, MySQL has no built in function to perform regular-expression substitution. For example, the Oracle dbms offers REGEXP_REPLACE (see here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm) but MySQL does no.

So you are going to have to use client code to retrieve each row with a date in the comment field, extract the date, and update the row. It's a nuisance, eh?

There is no problem going over the results in PHP. That's what I'm doing anyways. — Peon, Nov 12 '12 at 13:05

Find differently styled date in comments

2 Answers2

Linked