如何在 SQL 中将数字列格式化为电话号码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1426487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to format a numeric column as phone number in SQL
提问by avnic
I have table in the database with a phone number column. The numbers look like this:
我在数据库中有一个带有电话号码列的表。数字看起来像这样：
123456789
I want to format that to look like this:
我想将其格式化为如下所示：
123-456-789
回答by David Andres
This should do it:
这应该这样做：
UPDATE TheTable
SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' +
SUBSTRING(PhoneNumber, 4, 3) + '-' +
SUBSTRING(PhoneNumber, 7, 4)
Incorporated Kane's suggestion, you can compute the phone number's formatting at runtime. One possible approach would be to use scalar functions for this purpose (works in SQL Server):
结合凯恩的建议，您可以在运行时计算电话号码的格式。一种可能的方法是为此目的使用标量函数（适用于 SQL Server）：
CREATE FUNCTION FormatPhoneNumber(@phoneNumber VARCHAR(10))
RETURNS VARCHAR(12)
BEGIN
RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +
SUBSTRING(@phoneNumber, 4, 3) + '-' +
SUBSTRING(@phoneNumber, 7, 4)
END
回答by AdaTheDev
I'd generally recommend you leave the formatting up to your front-end code and just return the data as-is from SQL. However, to do it in SQL, I'd recommend you create a user-defined function to format it. Something like this:
我通常建议您将格式设置留给前端代码，并按原样从 SQL 返回数据。但是，要在 SQL 中执行此操作，我建议您创建一个用户定义的函数来对其进行格式化。像这样的东西：
CREATE FUNCTION [dbo].[fnFormatPhoneNumber](@PhoneNo VARCHAR(20))
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @Formatted VARCHAR(25)
IF (LEN(@PhoneNo) <> 10)
SET @Formatted = @PhoneNo
ELSE
SET @Formatted = LEFT(@PhoneNo, 3) + '-' + SUBSTRING(@PhoneNo, 4, 3) + '-' + SUBSTRING(@PhoneNo, 7, 4)
RETURN @Formatted
END
GO
Which you can then use like this:
然后您可以像这样使用它：
SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber
FROM SomeTable
It has a safeguard in, in case the phone number stored isn't the expected number of digits long, is blank, null etc - it won't error.
它有一个保护措施，以防存储的电话号码不是预期的数字位数、空白、空等 - 它不会出错。
EDIT:Just clocked on you want to update your existing data. The main bit that's relevant from my answer then is that you need to protect against "dodgy"/incomplete data (i.e. what if some existing values are only 5 characters long)
编辑：刚刚计时，您想更新现有数据。与我的回答相关的主要一点是，您需要防止“狡猾”/不完整的数据（即，如果某些现有值只有 5 个字符长会怎样）
回答by Hiram
Above users mentioned, those solutions are very basic and they won't work if the database has different phone formats like:
上面用户提到，这些解决方案非常基础，如果数据库具有不同的电话格式，它们将不起作用，例如：
(123)123-4564
123-456-4564
1234567989
etc
Here is a more complex solution that will work with ANY input given:
这是一个更复杂的解决方案，适用于给定的任何输入：
CREATE FUNCTION [dbo].[ufn_FormatPhone] (@PhoneNumber VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @Phone CHAR(32)
SET @Phone = @PhoneNumber
-- cleanse phone number string
WHILE PATINDEX('%[^0-9]%', @PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber, SUBSTRING(@PhoneNumber, PATINDEX('%[^0-9]%', @PhoneNumber), 1), '')
-- skip foreign phones
IF (
SUBSTRING(@PhoneNumber, 1, 1) = '1'
OR SUBSTRING(@PhoneNumber, 1, 1) = '+'
OR SUBSTRING(@PhoneNumber, 1, 1) = '0'
)
AND LEN(@PhoneNumber) > 11
RETURN @Phone
-- build US standard phone number
SET @Phone = @PhoneNumber
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' + SUBSTRING(@PhoneNumber, 4, 3) + '-' + SUBSTRING(@PhoneNumber, 7, 4)
IF LEN(@Phone) - 10 > 1
SET @PhoneNumber = @PhoneNumber + ' X' + SUBSTRING(@Phone, 11, LEN(@Phone) - 10)
RETURN @PhoneNumber
END
回答by Daniel Byrne
I do not recommend keeping bad data in the database and then only correcting it on the output. We have a database where phone numbers are entered in variously as :
我不建议将坏数据保存在数据库中，然后只在输出中更正它。我们有一个数据库，其中电话号码的输入方式多种多样：
- (555) 555-5555
- 555+555+5555
- 555.555.5555
- (555)555-5555
- 5555555555
- (555) 555-5555
- 555+555+5555
- 555.555.5555
- (555)555-5555
- 5555555555
Different people in an organization may write various retrieval functions and updates to the database, and therefore it would be harder to set in place formatting and retrieval rules. I am therefore correcting the data in the database first and foremost and then setting in place rules and form validations that protect the integrity of this database going forward.
组织中的不同人员可能会编写各种检索功能和对数据库的更新，因此很难设置适当的格式和检索规则。因此，我首先更正数据库中的数据，然后设置适当的规则和表单验证，以保护该数据库未来的完整性。
I see no justification for keeping bad data unless as suggested a duplicate column be added with corrected formatting and the original data kept around for redundancy and reference, and YES I consider badly formatted data as BAD data.
我认为没有理由保留错误数据，除非建议添加带有更正格式的重复列，并保留原始数据以备冗余和参考，是的，我将格式错误的数据视为错误数据。
回答by dasblinkenlight
Solutions that use SUBSTRINGand concatenation +are nearly independent of RDBMS. Here is a short solution that is specific to SQL Server:
使用SUBSTRING和串联的解决方案+几乎独立于 RDBMS。这是特定于 SQL Server 的简短解决方案：
declare @x int = 123456789
select stuff(stuff(@x, 4, 0, '-'), 8, 0, '-')
回答by atik sarker
You can also try this:
你也可以试试这个：
CREATE function [dbo].[fn_FormatPhone](@Phone varchar(30))
returns varchar(30)
As
Begin
declare @FormattedPhone varchar(30)
set @Phone = replace(@Phone, '.', '-') --alot of entries use periods instead of dashes
set @FormattedPhone =
Case
When isNumeric(@Phone) = 1 Then
case
when len(@Phone) = 10 then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 4, 3)+ '-' +substring(@Phone, 7, 4)
when len(@Phone) = 7 then substring(@Phone, 1, 3)+ '-' +substring(@Phone, 4, 4)
else @Phone
end
When @phone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 8, 4)
When @phone like '[0-9][0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 9, 4)
When @phone like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' Then '('+substring(@Phone, 1, 3)+')'+ ' ' +substring(@Phone, 5, 3)+ '-' +substring(@Phone, 9, 4)
Else @Phone
End
return @FormattedPhone
end
结尾
use on it select
使用它选择
(SELECT [dbo].[fn_FormatPhone](f.coffphone)) as 'Phone'
Output will be
输出将是
回答by Toprak
You Can Use FORMATif you column is a number Syntax like
FORMAT ( value, format [, culture ] ) In use like
FORMAT ( @d, 'D', 'en-US' )or FORMAT(123456789,'###-##-####')(But This works for only SQL SERVER 2012 And After)
如果您的列是数字，则可以使用FORMAT语法如 FORMAT ( value, format [,culture ] ) 使用中类似
FORMAT ( @d, 'D', 'en-US' )or FORMAT(123456789,'###-##-####')(但这仅适用于SQL SERVER 2012 及之后)
In Use Like
UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')
使用中喜欢
UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')
And
和
if your column is Varchar Or Nvarchar use do like this
CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),'',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )
如果您的列是 Varchar 或 Nvarchar 使用这样做
CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),'',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )
You can always get help from
您可以随时获得帮助
回答by nation161r
I found that this works if wanting in a (123) - 456-7890 format.
我发现这在需要 (123) - 456-7890 格式时有效。
UPDATE table
SET Phone_number = '(' +
SUBSTRING(Phone_number, 1, 3)
+ ') '
+ '- ' +
SUBSTRING(Phone_number, 4, 3)
+ '-' +
SUBSTRING(Phone_number, 7, 4)
回答by Josh Jay
Updated @sqiller's function for my purposes
为我的目的更新了@sqiller 的功能
CREATE FUNCTION [toolbox].[FormatPhoneNumber] (
@PhoneNumber VARCHAR(50),
@DefaultIfUnknown VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
-- remove any extension
IF CHARINDEX('x', @PhoneNumber, 1) > 0
SET @PhoneNumber = SUBSTRING(@PhoneNumber, 1, CHARINDEX('x', @PhoneNumber, 1) - 1)
-- cleanse phone number string
WHILE PATINDEX('%[^0-9]%',@PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber,
SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%',@PhoneNumber),1),'')
-- Remove US international code if exists, i.e. 12345678900
IF SUBSTRING(@PhoneNumber,1,1) = '1' AND LEN(@PhoneNumber) = 11
SET @PhoneNumber = SUBSTRING(@PhoneNumber, 2, 10)
-- any phone numbers without 10 characters are set to default
IF LEN(@PhoneNumber) <> 10
RETURN @DefaultIfUnknown
-- build US standard phone number
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3) + ') ' +
SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4)
RETURN @PhoneNumber
END
回答by Noe
If you want to just format the output no need to create a new table or a function. In this scenario the area code was on a separate fields. I use field1, field2just to illustrate you can select other fields in the same query:
如果您只想格式化输出，则无需创建新表或函数。在这种情况下，区号位于单独的字段上。我使用field1,field2只是为了说明您可以在同一查询中选择其他字段：
area phone
213 8962102
Select statement:
选择语句：
Select field1, field2,areacode,phone,SUBSTR(tablename.areacode,1,3) + '-' + SUBSTR(tablename.phone,1,3) + '-' + SUBSTR(tablename.areacode,4,4) as Formatted Phone from tablename
Sample OUTPUT:
示例输出：
columns: FIELD1, FIELD2, AREA, PHONE, FORMATTED PHONE
data: Field1, Field2, 213, 8962102, 213-896-2102
