SQL Server 2005 中的电话号码：第二部分 – 格式化
4.03/5 (9投票s)
在 SQL Server 中格式化电话号码。
引言
在我第一篇文章《SQL Server 2005 中的电话号码：第 1 部分 – 数据类型》中，我讨论了在 SQL Server 中存储简单电话号码的各种方法。直接显示 SQL Server 中存储的原始数据不适合人类阅读，因此必须进行某种形式的数据格式化。数据格式化通常在用户界面层进行，例如，通过 ASPX 页面或 WinForms 应用程序。通常，让 SQL Server 在视图中格式化数据以便传递到另一个文档（如可能难以正确格式化数字的报表）会很方便。在本文中，我将讨论几种可以在 SQL Server 上用于向用户呈现电话号码的格式化方法。
UDF
SQL Server 2000 引入了创建用户定义函数（UDF）的功能。使用自定义格式化函数来处理电话号码是 UDF 的一个绝佳示例。下面的代码可用于在 TSQL 中创建电话号码格式化函数。
-- =============================================
-- Author: Bryan Kowalchuk
-- Create date: Sept 14, 2007
-- Description: Formats a telephone number
-- to North American Numbering Plan standard
-- =============================================
CREATE FUNCTION [dbo].[FORMATPHONENUMBER]
(
@Number money
)
RETURNS varchar(25)
AS
BEGIN
-- Declare the return variable here
DECLARE @Formatted varchar(25) -- Formatted number to return
DECLARE @CharNum varchar(18) -- Character type of phone number
DECLARE @Extension int -- Phone extesion
DECLARE @Numerator bigint -- Working number variable
IF @Number IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
-- Just enough room, since max phone number
-- digits is 14 + 4 for extension is 18
-- Get rid of the decimal
SET @Numerator = CAST(@Number * 10000 AS bigint)
-- Cast to int to strip off leading zeros
SET @Extension = CAST(RIGHT(@Numerator, 4) AS int)
-- Strip off the extension
SET @CharNum = CAST(LEFT(@Numerator , LEN(@Numerator) - 4)
AS varchar(18))
IF LEN(@CharNum) = 10 -- Full phone number, return (905) 555-1212
BEGIN
SET @Formatted = '(' + LEFT(@CharNum, 3) + ') ' +
SUBSTRING(@CharNum,4,3) + '-' + RIGHT(@CharNum, 4)
IF @Extension > 0 -- Add Extension
BEGIN
SET @Formatted = @Formatted + ' ext '+
CAST(@Extension AS varchar(4))
END
RETURN @Formatted
END
IF LEN(@CharNum) = 7 -- No Area Code, return 555-1212
BEGIN
SET @Formatted = LEFT(@CharNum, 3) + '-' + RIGHT(@CharNum, 4)
IF @Extension > 0 -- Add Extension
BEGIN
SET @Formatted = @Formatted + ' ext '+
CAST(@Extension AS varchar(6))
END
RETURN @Formatted
END
IF LEN(@CharNum) = 11
-- Full phone number with access code,
-- return 1 (905) 555-1212 (19055551212)
BEGIN
SET @Formatted = LEFT(@CharNum, 1) + ' (' + SUBSTRING(@CharNum, 2, 3) + ') ' +
SUBSTRING(@CharNum,4,3) + '-' + RIGHT(@CharNum, 4)
IF @Extension > 0 -- Add Extension
BEGIN
SET @Formatted = @Formatted + ' ext '+ CAST(@Extension AS varchar(4))
END
RETURN @Formatted
END
-- Last case, just return the number unformatted (unhandled format)
SET @Formatted = @CharNum
IF @Extension > 0 -- Just the Extension
BEGIN
SET @Formatted = @Formatted + ' ext '+ CAST(@Extension AS varchar(4))
RETURN 'ext '+ CAST(@Extension AS varchar(4))
END
RETURN @Formatted
END
CLR 版本
SQL Server 2005 允许使用您选择的 .NET 编程语言编写自己的 DLL，因此我也用 C# 编写了等效的函数。要在 Visual Studio 2005 中执行此操作，请启动一个新的 SQL Server 项目，并添加一个新的用户定义函数。将此代码粘贴到其中。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
IsDeterministic=true, IsPrecise=true)]
public static SqlString FormatNAPhoneNumber(SqlMoney Number)
{
// Return Null if number is null
if (Number.IsNull)
return SqlString.Null;
string phoneNumber = Number.ToString();
string [] phone = phoneNumber.Split(new Char [] {'.'});
string charnum = phoneNumber; //Default to unformatted number
int extension = int.Parse(phone[1]);
switch (phone[0].Length)
{
case 10:
//Format standard North American phone number
//'(416) 555-1212' or '(415) 555-1212 ext1234'
{
if (extension > 0)
{
charnum = string.Format("({0}) {1}-{2} ext{3}",
phone[0].Substring(0, 3),
phone[0].Substring(3, 3),
phone[0].Substring(6),
extension.ToString());
// charnum = Regex.Replace(phone[0], @"(\d{3})(\d{3})(\d{4})",
// "($1) $2-$3"); //Test Regex performance
// charnum = "(" + phone[0].Substring(0, 3) + ") " +
// phone[0].Substring(3, 3) + //Test standard string concat
// phone[0].Substring(6) + " ext" + extension.ToString();
return new SqlString(charnum);
}
else
{
charnum = string.Format("({0}) {1}-{2}",
phone[0].Substring(0, 3),
phone[0].Substring(3, 3),
phone[0].Substring(6));
// charnum = Regex.Replace(phone[0], @"(\d{3})(\d{3})(\d{4})",
// "($1) $2-$3");
// charnum = "(" + phone[0].Substring(0, 3) + ") " +
// phone[0].Substring(3, 3) + phone[0].Substring(6);
return new SqlString(charnum);
}
break;
}
case 7: // Format without the area code '555-1212' or '555-1212 ext1234'
{
if (extension > 0)
{
charnum = string.Format("{0}-{1} ext{2}",
phone[0].Substring(0, 3),
phone[0].Substring(3),
extension.ToString());
return new SqlString(charnum);
break;
}
else
{
charnum = string.Format("{0}-{1}",
phone[0].Substring(0, 3),
phone[0].Substring(3));
return new SqlString(charnum);
break;
}
}
case 11:
// Format with dialing prefix '1 (416) 555-1212' or '1 (416) 555-1212 ext1234'
{
// return new SqlString(phone[0] + " and " + phone[1]);
if (extension > 0)
{
charnum = string.Format("{0} ({1}) {2}-{3} ext{4}",
phone[0].Substring(0, 1),
phone[0].Substring(1, 3),
phone[0].Substring(4, 3),
phone[0].Substring(7),
extension.ToString());
return new SqlString(charnum);
}
else
{
charnum = string.Format("{0} ({1}) {2}-{3}",
phone[0].Substring(0, 1),
phone[0].Substring(1, 3),
phone[0].Substring(4, 3),
phone[0].Substring(7));
return new SqlString(charnum);
}
}
default: //Just return the number as a string, no formatting
{
return new SqlString(charnum);
break;
}
}
}
}
我对一个包含电话号码数据类型的小型表进行了快速基准测试，以查看其对性能的影响。该表包含 580 行数据，并执行了 1000 次 SQL SELECT 命令，总共调用了 580,000 次函数。执行 SELECT 命令时未将数据集返回到客户端，以最大程度地减少网络时间。执行时间以毫秒为单位。您的结果可能会有所不同，但请将这些数字作为不同方法的相对比较。
|
方法 |
时间 |
函数开销 |
|
不进行电话号码格式化 |
1950 毫秒 |
|
|
TSQL |
7450 毫秒 |
5500 毫秒 |
|
使用 |
4750 毫秒 |
2800 毫秒 |
|
使用 RegEx 的 C# 函数 |
7187 毫秒 |
5237 毫秒 |
令人惊讶的结果！
令我惊讶的是，.NET 函数的运行速度是原生 TSQL 函数的两倍！我最初的猜测是，原生 TSQL 函数肯定会运行得更快，因为它们消除了 .NET 引擎和接口的开销。看来微软在 .NET 集成方面做得功课很足。这是另一个很好的例子，说明测试结果常常与您的预期相反。
C# 版本还注释掉了各种行，如果您选择使用 RegEx 库或简单的字符串连接运算符来格式化电话号码。处理国际电话号码的一种方法是将 RegEx 模式和求值器字符串存储在按国家/地区键入的一个表中。然后，可以将此表与电话号码关联，并将相应的格式化代码传递给函数。我没有在设计中深入研究这一点，但我尝试使用 RegEx 方法进行格式化，以测试其性能与使用 String.Format 方法相比。正如您所见，它的性能与 TSQL 版本差不多，因此 RegEx 对象的开销相当可观。
结论
使用 TSQL 编写用户定义函数可能会很麻烦，特别是当它需要大量的字符串操作或复杂的逻辑时。使用 .NET CLR 集成，您可以轻松地利用 .NET 库的全部功能，并且性能可能比 TSQL 更好。
在我第一篇文章中提到，细节决定成败。通过关注正确的ю设计并进行一些测试，我们将存储电话号码的数据占用空间减少了一半，并将格式化号码的 CPU 需求也减少了一半。像这样的微小改变，对于您的应用程序的可扩展性来说，可能产生巨大的影响。