MySQL strip non-numeric characters to compare

Question

I'm looking to find records in a table that match a specific number that the user enters. So, the user may enter 12345, but this could be 123zz4-5 in the database.

I imagine something like this would work, if PHP functions worked in MySQL.

SELECT * FROM foo WHERE preg_replace("/[^0-9]/","",bar) = '12345'

What's the equivalent function or way to do this with just MySQL?

Answer 1

The simplest way I can think to do it is to use the MySQL REGEXP operator a la:

WHERE foo LIKE '1\D*2\D*3\D*4\D*5'

It's not especially pretty but MySQL doesn't have a preg_replace function so I think it's the best you're going to get.

Personally, if this only-numeric data is so important, I'd keep a separate field just to contain the stripped data. It'll make your lookups a lot faster than with the regular expression search.

Answer 2

While it's not pretty and it shows results that don't match, this helps:

SELECT * FROM foo WHERE bar LIKE = '%1%2%3%4%5%'

I would still like to find a better solution similar to the item in the original question.

Answer 3

There's no regex replace as far as I'm concerned, but I found this solution;

--Create a table with numbers
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);

INSERT INTO ints (i) VALUES
( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

--Then extract the numbers from the specified column
SELECT
    bar,
    GROUP_CONCAT(SUBSTRING(bar, i, 1) ORDER BY i SEPARATOR '')
FROM foo
JOIN ints ON i BETWEEN 1 AND LENGTH(bar)
WHERE
    SUBSTRING(bar, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY bar;

It works for me and I use MySQL 5.0

Also I found this place that could help.

Answer 4

How big is table with foo? If it is small, and speed really doesn't matter, you might pull the row ID and foo, loop over it using the PHP replace functions to compare, and then pull the info you want by row number.

Of course, if the table is too big, this won't work well.

Answer 5

There's no regexp replace, only a plain string REPLACE().

MySQL has the REGEXP operator, but it's only a match tester not a replacer, so you would have to turn the logic inside-out:

SELECT * FROM foo WHERE bar REGEXP '[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*';

This is like your version with LIKE but matches more accurately. Both will perform equally badly, needing a full table scan without indexes.