Need a help to write search query

Question

My database contains a list of phone numbers which is of varchar type. Phone number may be in any of these formats

12323232323
1-232-323 2323
232-323-2323
2323232323

Instead of the – symbol there may be ( ) , . or space And if I search for 12323232323, 1-232-323 2323, 232-323-2323, or 2323232323 it should display all these results. I need to write a query for this.

Answer 1

I think it is not efficient to do this realtime, I propose two options.

1. clean the data, so there will be only one format.

2. add another column which contains the clean data, so when you search, you search for this column, when display you can display the various format data.

Answer 2

I agree with James, but if you really need to search the database as it is, perhaps MySQL's REPLACE operator will get you where you need to go. Something like

select * from mytable where replace(crazynumber,'-','')='23232323';