如何按格式错误的列选择行

Question

I have got a very old database, where someone didn't keep valid format for data. So in my Column I have got values like that:

in column is   1234567890             expected   1234567890
in column is   12-345-678-9-0 foo     expected   1234567890
in column is   bar 1234-567-890       expected   1234567890
in column is   12-34567-foobar-890    expected   1234567890
etc.

I mean, there is a number (always 10 digits), with some other characters in random places.

I have got in my variable $number the integer value of 10 digits.

I want to do some query like SELECT * FROM table WHERE Column = $number, but I want to match all rows that has got this $number, if I would clean my column to right format.

I have no idea, how can I do this :(

Answer 1

To convert data from this alphanumerical column I see two possibilities:

1. Read all entries from the table (via php, as you already specified as tag), change it with a regular expression $newValue = preg_replace('/\D/', '', $oldValue); and update the database entry with that value.

or

1. Use a user defined function like MySQL UDF RegExp to do that directly with a MySQL update statement.