CODEWITHSUNDEEP
phpmysqldatabaseescaping
Kiel
Kiel

Reputation: 493

Escape special characters from the database - MySql

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.

Upvotes: 1

Views: 717

Answers (4)

metalfight - user868766
metalfight - user868766

Reputation: 2750

@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 !

Upvotes: 2

Ranjit Singh
Ranjit Singh

Reputation: 3735

Replace() is the best function to do this : 

Select * from TableName where Replace(ColumnName,'escaping character','replaced character')='your Search option'

for your case escaping character is dot(.), replaced character is '' and search option is '123456789'

Upvotes: 0

solaimuruganv
solaimuruganv

Reputation: 29807

select phone from table_name 
 where  replace (cast (phone as text) , '.','')  like '%123456789%'

Upvotes: 2

eggyal
eggyal

Reputation: 126005

You could use MySQL's REPLACE() function:

SELECT * FROM my_table WHERE REPLACE(my_column, '.', '') = '123456789';

But if my_column just contains integers, you should really change it to one of MySQL's integer types.

Upvotes: 1

Related Questions