需要帮助来编写搜索查询

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

我的数据库包含一个varchar类型的电话号码列表。电话号码可以是任何这些格式

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.

而不是 - 符号可能有(),.或空格如果我搜索12323232323,1-232-323 2323,232-323-2323或2323232323,它应显示所有这些结果。我需要为此编写一个查询。

3 个解决方案

#1

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

我认为实时做这个效率不高,我提出了两种选择。

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

清理数据,因此只有一种格式。
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.

添加另一个包含干净数据的列,因此在搜索时,搜索此列,在显示时可以显示各种格式数据。

#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

我同意James的意见,但是如果你真的需要按原样搜索数据库,那么MySQL的REPLACE运算符可能会让你到达目的地。就像是

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

#3

How to Replace Multiple Characters in SQL?

如何在SQL中替换多个字符?

Can MySQL replace multiple characters?

MySQL可以替换多个字符吗?

Agree with James, but if u really need to do this, the above two links have proposed the prefect solutions for your scenario.

同意James,但如果你真的需要这样做,上面两个链接已经为你的场景提出了完美的解决方案。

#1