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?
Speed is not important.
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.