Regular expressions to help in Finding and Replacing DML statements

July 6th, 2009

Recently, we were updating our own database used for dbForge Data Compare for MySQL testing. I wanted to add several tables with data, using a dump file of one of the tables.

But how to drop a millisecond part in every pasted string, I mean a date like 2006-12-24 15:35:19.658′ should be replaced with ‘2006-12-24 15:35:19’ one?

Any simple Find and Replace functionality won’t help, as I don’t know what to find and with what to replace.




So what did I do? I completed this task using dbForge Studio for MySQL and regular expressions.  Regular expressions provide a concise and flexible means for identifying strings of text of interest, such as particular characters, words, or patterns of characters.

Follow these steps to complete the task:

1. In the top menu of dbForge Studio for MySQL open the Find and Replace window.

Open Find and Replace window

Open Find and Replace window

2. Tick the Use check box and select Regular Expressions, so the program will use them while finding and replacing the data.

Use Regular Expressions

Use Regular Expressions

3. Fill the Find and Replace fields with the pattern like this:
Find what:  {:z::z::z}.:z
Replace with: 1

Fill Find what and Replace with

Fill Find what and Replace with

What do the entered symbols mean for the program?

:z – to search for an integer number of any length, e.g.,  15 or 19, or 52
: – to search for a semicolon delimiter ( : )
{} – a pattern , specified in the brackets, will be used for replacement
. – to search a period ( . )
1 – to use the first pattern

The program will search dates matching the specified pattern, i.e., it will replace dates in ‘hour:min:sec.msec’ format with ‘hour:min:sec’ one, truncating milliseconds.

Result of replacing

Result of replacing

Find more information about regular expression usage in “Regular Expressions for Find and Replace” topic of dbForge Studio for MySQL help

3 Responses to “Regular expressions to help in Finding and Replacing DML statements”

  1. Shlomi Noach Says:


    If you have a dump, I believe using sed would word best. Here’s an ugly one off the sleeve:

    gunzip < my_dump.sql.gz | sed “s/([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]).[0-9]{1,3}/1/g” | mysql

    The same technique can be used to import data, while ALTERing tables from MyISAM to InnoDB, etc.
    When data is imported, it is already in correct format.

    I assume the above code needs some tuning; it’s just an example.


  2. EMComments Says:

    “Any simple Find and Replace functionality won’t help, as I don’t know what to find and with what to replace.”

    In what way do you not know what to find? It is the leftmost 19 characters. I’m sure that this would be horribly inefficient and that regex has much greater flexibilty but:

    UPDATE date_time_table SET date_time_column=LEFT(date_time_column,12);

    Am I missing something here?

  3. Helen G Says:

    To EMComments:

    Note, the actions, described in the article, are intended for working with scripts not with a live database. Definitely, your variant can be used for the script, but with some changes. We can use a usual Find and Replace functionality (without any regular expressions) to transform INSERT INTO date_time_table (date_time_column) VALUES (‘2006-12-24 15:35:19.658’); into INSERT INTO date_time_table (date_time_column) VALUES (LEFT(‘2006-12-24 15:35:19.658’, 19));

    The steps will be the following:

    1. Replace “VALUES (‘” with “VALUES (LEFT(‘”
    2. Replace “‘);” with “‘, 19));”

    But this approach will increase the script size and decrease readability, which is undesirable for the scripts kept in a version control system.