MYSQL - searching strings within a select

Question

Hi,

I want to be able to search a field in the database and see if the "changed from" and "to" values are different(the values are stored within a string in a single field).

Here is an example of what the string in the field looks like

Instance Person(34) modified by 1.
Field phone_number changed from "123" to "123".
Current field values are:
    first_name => "alex"
    last_name => "Handley"

In this example they are the same so would not be returned.

is it possible to do this ?

Alex

Answer 1

This is really a job for a regular expression, not a database query.

You could do it with a stored procedure which makes a query, chops up the result, and compares the two parts. But, again, this type of logic belongs at the application layer - either beforehand, storing the parts of the string you'll need to compare separately, or afterwards, extracting the useful info from the single database column.

Answer 2

MySQL has pretty nice regular expression support. (It's also fast; I've found that REGEXP is often faster than LIKE).

I don't believe that it has back reference support, so it would be difficult to compare in a single query. You might be able to do it with a clever join or a stored procedure.