从数据库中逃避特殊字符 - MySql

Question

Hi guys I need to search for a data in the database like 123.456.789 how can I search it even if I only entered 123456789 ? I need to escape the special characters from the database so that even if i search for 123456789 it can also display values like 123.456.789.

Here is my query:

SELECT *
FROM clients 
WHERE REPLACE(phone, '.', '') LIKE ".$searchtext."

... where searchtext is the number im looking for. It should return all values that match regardless of whatever special characters are present.

Answer 1

@Kiel

Here is the sample table & query. Please see if this can help you. Not sure about your table structure.

 CREATE TABLE `clients` (
 `id` int(11) NOT NULL,
 `phone` varchar(255) NOT NULL
 ) ENGINE=InnoDB;

INSERT INTO `test`.`clients` (
`id` ,
`phone`
)
VALUES (
 '1', '123.456.789'
), (
'2', '123.456.785'
);



mysql> select * from clients where replace(phone,'.','') = '123456789'; 
+----+-------------+
| id | phone       |
+----+-------------+
|  1 | 123.456.789 |
+----+-------------+

Hope this help !