如何格式化数字列在SQL电话号码(How to format a numeric column as

2019-07-18 02:22发布

站内文章 / 移动开发

4 0

Answer 1:

这应该这样做：

UPDATE TheTable
SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' + 
                  SUBSTRING(PhoneNumber, 4, 3) + '-' + 
                  SUBSTRING(PhoneNumber, 7, 4)

凯恩法团的建议，你可以在运行时计算出的电话号码的格式。一种可能的方法是使用标量函数用于此目的（在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

Answer 2:

我一般会建议你离开格式到您的前端代码和刚刚回归的数据，是从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

然后你可以使用这样的：

SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber
FROM SomeTable

它有一个保障，如果储存在电话号码不长位数的预期数量，为空，空等等 - 它不会出错。

编辑：只是主频上要更新现有数据。这是从我的答案相关的主要位则是需要防范“狡猾” /不完整的数据（即如果一些现有的值仅5个字符长）

Answer 3:

如上面所提到的用户，这些解决方案是非常基本的，如果数据库有不同的电话格式，比如他们将无法正常工作：(123)123-4564 123-456-4564 1234567989等

这里是一个更复杂的解决方案，将与给定的任何输入的工作：

    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

Answer 4:

我不建议在数据库中保存坏的数据，然后只纠正它的输出。我们这里的电话号码在不同的进入了数据库：

（555）555-5555
555 + 555 + 5555
555.555.5555
(555)555-5555
5555555555

不同的人在一个组织可以写不同的检索功能，并更新到数据库中，因此这将是很难到位的格式和检索的规则设置。我因此在数据库中首先纠正数据，然后在适当的规则，并保护该数据库的前进完整性表单验证设置。

我认为没有理由保持不错的数据，除非是建议与纠正格式添加了重复的列和原始数据保持在大约冗余和参考，是的，我认为格式错误数据为错误数据。

Answer 5:

使用解决方案SUBSTRING和串联+几乎是独立的RDBMS的。下面是一个简短的解决方案，是特定于SQL Server：

declare @x int = 123456789
select stuff(stuff(@x, 4, 0, '-'), 8, 0, '-')

Answer 6:

你也可以试试这个：

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

结束

用它选择

(SELECT [dbo].[fn_FormatPhone](f.coffphone)) as 'Phone'

输出将被

Answer 7:

我发现这个作品，如果想在（123） - 456-7890格式。

UPDATE table 
SET Phone_number =  '(' +  
                    SUBSTRING(Phone_number, 1, 3) 
                    + ') ' 
                    + '- ' +
                    SUBSTRING(Phone_number, 4, 3) 
                    + '-' +
                    SUBSTRING(Phone_number, 7, 4)

Answer 8:

更新@ 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

Answer 9:

如果你想只格式化输出无需创建一个新的表或函数。在这种情况下区号是在一个单独的领域。我用field1 ， field2只是为了说明，您可以选择在同一查询等多个领域：

area  phone
213   8962102

Select语句：

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

输出示例：

columns: FIELD1, FIELD2, AREA, PHONE, FORMATTED PHONE
data:    Field1, Field2, 213,  8962102,  213-896-2102

Answer 10:

如果你列了一些语法，你可以使用格式为FORMAT（值，格式[文化]）在类似的使用FORMAT ( @d, 'D', 'en-US' )或FORMAT(123456789,'###-##-####')但是这适用于只有SQL SERVER 2012后 ）

在使用像UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')

和

如果你的列是VARCHAR或nvarchar使用做这样的CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),' ',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )

您可以随时得到帮助