Escape special characters from the database - 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.

@user868766 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. — Kiel, Oct 17 '12 at 10:47
It's the first time I see regular dot considered a special character... :) — Álvaro González, Oct 17 '12 at 11:00
select * from clients where replace(phone,'.','') = '123456789'; Is working fine with sample data i created. can you show us sample data / table structure. — metalfight - user868766, Oct 17 '12 at 11:00
@user868766 what data type did you use for phone? mine was varchar and it return empty result. Query `SELECT * FROM clients WHERE REPLACE(phone, '.', '') LIKE ".$searchtext."` the value for searchtext is 123456789 it should output 123.456.789 — Kiel, Oct 17 '12 at 11:07
It would be better to store phone numbers in a standardised format see http://stackoverflow.com/questions/41925/is-there-a-standard-for-storing-normalized-phone-numbers-in-a-database It would also help if you added phone number into title — david strachan, Oct 17 '12 at 11:12
@davidstrachan that was our mistake. since we are handling different countries with different phone formats we set the phone number as varchar data type. — Kiel, Oct 17 '12 at 11:19
@user868766 it worked i added a % symbol after the searchtext here is my query `SELECT * FROM clients WHERE REPLACE(phone, '.', '') LIKE '".$searchtext."%'` how about if i want to display also values that contain '-' and '' like 123-456-789 or 123 456 789 — Kiel, Oct 17 '12 at 11:27

score 2 · Answer 1 · answered Oct 17 '12 at 10:49

2

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

answered Oct 17 '12 at 10:49

solaimuruganv

27,177
1
18
23

score 2 · Accepted Answer · answered Oct 17 '12 at 11:28

2

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

answered Oct 17 '12 at 11:28

metalfight - user868766

2,722
1
16
20

thanks for this! now i need to replace . - and space so that i can get 123.456.789 , 123-456-789 and 123 456 789 any idea? im thinking of adding OR in my query but is there any easier way? – Kiel Oct 17 '12 at 11:34
I did it thanks! here is my final query `SELECT * FROM clients WHERE REPLACE(REPLACE(REPLACE(phone, '.', ''),'-',''),' ','') LIKE '".$searchtext."%' "` THANKS! – Kiel Oct 17 '12 at 11:42

score 1 · Answer 3 · answered Oct 17 '12 at 10:40

1

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.

answered Oct 17 '12 at 10:40

eggyal

122,705
18
212
237

Hi I did this but it was not successful this is my query: `SELECT * FROM clients WHERE REPLACE(phone, '.', '') LIKE ".$searchtext."` where searchtext is the number im searching for. – Kiel Oct 17 '12 at 10:45

score 0 · Answer 4 · answered Oct 17 '12 at 11:20

0

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'

answered Oct 17 '12 at 11:20

Ranjit Singh

3,715
1
21
35

I want to escape the # character, but i cant, when i escaping i have 0 results, with , - and black space works... but #didnot work `SELECT id, titlu, link, poza, alt, keywords FROM istorie WHERE replace(replace(replace(replace(keywords, ',', ''), '-', ''), ' ', ''), '#', '') LIKE '%$keyword%'` - 0 results and the two query was 1 - (#test, bla) and the other (test, bla) without #, if `SELECT 8 FROM istorie WHERE replace(replace(replace(keywords, ',', ''), '-', ''), ' ', '')) LIKE '%$keyword%'` is giveing me only 1 results the query value(#test, bla) – Stefan J. Oct 18 '16 at 18:35

Escape special characters from the database - MySql

4 Answers4