比较电话号码sql

本文介绍了比较电话号码sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在 2 个不同的表中有 2 组电话号码，table 1 具有 01234567890 的直接格式，另一个表有 3 种不同的格式，有时是 01234567890 或 01234 567890 或 01234-567890.

I have 2 sets of phone numbers in 2 different tables, table 1 has the straight forward format of 01234567890 and the other table has 3 different formats, sometimes it's 01234567890 or 01234 567890 or 01234-567890.

目前我只是对表进行内部联接，只返回几行，但希望更多，因为显然联接中会遗漏任何带有连字符或空格的内容.

Currently I'm just doing an Inner join on the tables and only get a few rows returned but would expect more as obviously anything with a hyphen or space will get missed from the join.

系统的方式我无法更改具有 3 种格式的表格中的数据，因此无法对其进行标准化或清除等.

The way the system is I can't change the data in the table with 3 formats so can't standardise it or clean it out etc.

解决这个问题的最佳方法是什么?

What would be the best way to tackle this?

推荐答案

好吧，您可以考虑在您的第二个表中创建一个计算列来规范化电话号码格式 - 类似于:

Well you could think about creating a computed column in your second table to normalize the phone number format - something like:

ALTER TABLE dbo.YourSecondTable ADD NormalizedPhone AS REPLACE(REPLACE(PhoneColumn, '-', ''), ' ', '') PERSISTED

此表达式从 PhoneColumn 中删除任何空格和任何破折号，并将这些值存储在名为 NormalizedPhone 的新计算列中.

This expression removes any spaces and any dashes from the PhoneColumn and those values are stored in a new, computed column called NormalizedPhone.

此列将始终保持最新 - 即使您稍后更改了 PhoneColumn 的值.它将始终包含标准化电话号码自动.

This column will always be kept up to date - even if you change your PhoneColumn's value later on. It will always contain the normalized phone number automagically.

现在您可以轻松地将规范化电话字符串的两个表连接起来，并且您应该得到更准确的结果.

Now you can easily join the two tables on the normalized phone strings, and you should get more accurate results.