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.
You can remove special characters from a database field using REPLACE() function.
I realise that this is an ancient topic but upon googling this problem I couldn't find a simple solution (I saw the venerable agents but think this is a simpler solution) so here's a function I wrote, seems to work quite well.
DROP FUNCTION IF EXISTS STRIP_NON_DIGIT; DELIMITER $$ CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE output VARCHAR(255) DEFAULT ''; DECLARE iterator INT DEFAULT 1; WHILE iterator < (LENGTH(input) + 1) DO IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN SET output = CONCAT(output, SUBSTRING(input, iterator, 1)); END IF; SET iterator = iterator + 1; END WHILE; RETURN output; END $$
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With