How to search for a phone number in mysql database using php?

Question

PHP

Prompt in the database there are phones with the record type
8903ххх-хх-хх,
+7903ххххххх
8-903-хх-хх-ххх
7-915-хх-ххх-хх ххххххх
value is entered in the search field If through a regular LIKE query Displays only one format, how can you get the search to cover other record formats
SELECT * FROM test WHERE LIKE %xxxxxxx%

Answer 1

No way. Before saving to the database, you need to normalize the numbers

Answer 2

It is possible through REGEXP_REPLACE () to convert data to the database on the fly, and then search for it further.
https://dev.mysql.com/doc/refman/8.0/en/regexp.htm...
remove everything except numbers from phone column values

SELECT * FROM table 
WHERE REGEXP_REPLACE(phone, '[^0-9]+', '') LIKE '79xxxx'

Answer 3

It's not too late to normalize the data. You can create an additional VARCHAR(20) data column and normalize phones there. Or migrate the entire database on the same column.
What is the best way to store phone numbers in the database?
https://stackoverflow.com/questions/8284647/how-to...
https://ru.wikipedia.org/wiki/E.164