MySql telephone search

[nishika]

There's a table with users and there's a cell with a phone number. All telephones are recorded in format: 8 (999) 999-99-99

How to make a request so that the user can introduce such formats to search: 999-99-99 or 9999999. ♪ ♪

[Bogopo]

http://sqlfiddle.com/#!9/4f7c1/2

MySQL 5.6 Schema Setup:

create table tel (
  tel nvarchar(32)
);
insert into tel(tel) values (N'8 (999) 999-99-99');

insert into tel(tel) values (N'8 (123) 123-12-13');

insert into tel(tel) values (N'8 (444) 444-12-99');

insert into tel(tel) values (N'8 (456) 888-89-98');

insert into tel(tel) values (N'8 (000) 009-09-09');

insert into tel(tel) values (N'8 (009) 909-09-09');

Query:

select tel from tel where INSTR(REPLACE(REPLACE(REPLACE(REPLACE(tel,N' ',N''),N'-',N''),N'(',N''),N')',N''), N'99')

http://sqlfiddle.com/#!9/4f7c1/2/0 :

|               tel |

|-------------------|

| 8 (999) 999-99-99 |

| 8 (444) 444-12-99 |

| 8 (456) 888-89-98 |

| 8 (009) 909-09-09 |

Living, of course, but something's not better. Actually, it's better not to keep the formatted data in the database, especially if the format is always the same.