在数据库中搜索多种电话号码格式 [英] Search for multiple phone number formats in database

问题描述

我有一个带有用户表的数据库.该表包含列phonenumber.问题在于它的字段使用多个数字模式.

I got a database with a user table. This table contains a column phonenumber. The problem is that its fields use multiple number patterns.

我发现的当前模式:

• 06403/975-0
• [phone]
• 06403 975-0
• 06403 975 0
• [phone]

在数据库中搜索用户时，是否可以搜索所有数字模式?

When searching for a user in the database, is there a way to search for all number patterns?

SELECT id FROM user WHERE phone = '0123456789'

我使用Oracle和MS SQL

I use Oracle and MS SQL

推荐答案

假设您的问题是这样的:

Assuming your question means this:

在WHERE子句中进行比较之前，是否可以从存储的电话号码中删除所有非数字字符?"

"Is it possible to remove all the non-digit characters from the stored phone number, before making the comparison in the WHERE clause?"

可能的解决方案如下:

... 
where translate(phone, '0123456789' || phone, '0123456789') = <input value here>

TRANSLATE会将每个数字转换为自身，而phone中的所有其他字符转换为零(将它们从字符串中删除).这正是您想要的.

TRANSLATE will translate every digit to itself, and all other characters in phone to nothing (they will simply be deleted from the string). This is exactly what you want.

如果发现查询速度很慢，则可能要在translate(phone, '0123456789' || phone, '0123456789')上创建(基于函数的)索引.

If you find that the query is slow, you may want to create a (function-based) index on translate(phone, '0123456789' || phone, '0123456789').

编辑:我错过了您所说的同时使用Oracle和SQL Server的部分.我进行了快速搜索，发现SQL Server没有类似于Oracle TRANSLATE的功能.我将把它留给SQL Server专家来帮助您完成这一部分.我不知道SQL Server.

EDIT: I missed the part where you said you are using both Oracle and SQL Server. I did a quick search and found that SQL Server does not have a function similar to Oracle's TRANSLATE. I will leave it to SQL Server experts to help you with that part; I don't know SQL Server.

这篇关于在数据库中搜索多种电话号码格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！