Removing white space non-numeric characters parenthesis spaces hyphens of Phone Number formatting in MySQL Query Statement

This is God of All Small Things.

You have phone number with too many formattting like
(444) 444-4444
(555)555-5555
800-123-4567
877 987-4563

and you want to QUERY it in MySQL like "4085439351" --- you will have to write a trigger and use it as function in your MYSQL QUERY STATEMENT

STEP#1 -- WRITE A QUERY

// phone10() is your function name DELIMITER $$ USE [database]$$ // USE A DATABASE WHERE YOU WANT TO SAVE YOUR TRIGGER DROP FUNCTION IF EXISTS `phone10`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `phone10`(str VARCHAR(1000)) RETURNS VARCHAR(1000) CHARSET latin1 DETERMINISTIC BEGIN DECLARE counter INT DEFAULT 0; DECLARE strLength INT DEFAULT 0; DECLARE strChar VARCHAR(1000) DEFAULT '' ; DECLARE retVal VARCHAR(1000) DEFAULT ''; SET strLength = LENGTH(str); WHILE strLength > 0 DO SET counter = counter+1; SET strChar = SUBSTRING(str,counter,1); IF strChar REGEXP('[0-9]+') = 1 THEN SET retVal = CONCAT(retVal,strChar); END IF; SET strLength = strLength -1; SET strChar = NULL; END WHILE; RETURN retVal; END $$ DELIMITER ;

MYSQL QUERY STATEMENT

SELECT person FROM usermst WHERE phone10(phone_number) = '123456789' select phone10(phone_number) from usermst

SIMPLE - ISN'T IT???