Comment formater une colonne numérique en tant que numéro de téléphone en SQL

je vous recommande généralement de laisser le formatage à votre code front-end et de simplement retourner les données as-is À partir de SQL. Cependant, pour le faire en SQL, je vous recommande de créer une fonction définie par l'utilisateur pour le formater. Quelque chose comme ceci:

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

que vous pouvez ensuite utiliser comme ceci:

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

il a une sauvegarde, dans le cas où le numéro de téléphone stocké n'est pas le nombre prévu de chiffres de long, est blanc, null etc - il ne sera pas d'erreur.

EDIT: Juste pointé sur vous souhaitez mettre à jour vos données existantes. Le principal point pertinent de ma réponse est que vous devez vous protéger contre les données "douteuses" / incomplètes (c'est-à-dire que faire si certaines valeurs existantes ne font que 5 caractères de long)

Je ne recommande pas de conserver les mauvaises données dans la base de données et de les corriger uniquement sur la sortie. Nous avons une base de données où les numéros de téléphone sont entrés dans diversement :

• (555) 555-5555
• 555+555+5555
• 555.555.5555
• (555)555-5555
• 5555555555

différentes personnes dans une organisation peuvent écrire diverses fonctions de récupération et des mises à jour à la base de données, et il serait donc plus difficile de définir règles de formatage et de récupération en place. Je corrige donc d'abord et avant tout les données de la base de données, puis je mets en place des règles et des validations de forme qui protégeront l'intégrité de cette base de données à l'avenir.

Je ne vois aucune justification pour conserver de mauvaises données à moins que comme suggéré une colonne dupliquée soit ajoutée avec le formatage corrigé et les données originales conservées autour de la redondance et de référence, et oui je considère les données mal formatées comme de mauvaises données.

comme mentionné ci-dessus utilisateurs, ces solutions sont très basiques et ils ne fonctionneront pas si la base de données a différents formats de téléphone comme: (123)123-4564 123-456-4564 Milliard deux cent trente quatre million cinq cent soixante sept mille neuf cent quatre vingt neuf etc

Voici une solution plus complexe qui fonctionnera avec N'importe quelle entrée donnée:

    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

Solutions qui utilisent SUBSTRING et la concaténation + sont presque indépendants des RDBM. Voici une solution courte qui est spécifique à SQL Server:

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

Vous pouvez aussi essayer ceci:

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

Utilisez sur elle sélectionnez

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

Sortie sera

j'ai trouvé que cela fonctionne si vous voulez dans un format (123) - 456-7890.

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

mise à Jour de @sqiller fonction de mes besoins

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

si vous voulez simplement formater la sortie, pas besoin de créer une nouvelle table ou une fonction. Dans ce scénario, l'indicatif régional figurait dans des champs distincts. J'utilise field1,field2 juste pour illustrer, vous pouvez sélectionner d'autres champs dans la même requête:

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

sortie de L'échantillon:

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

Vous Pouvez Utiliser FORMAT si votre colonne est une syntaxe de nombre comme FORMAT (valeur, format [, culture ] ) utilisé comme FORMAT ( @d, 'D', 'en-US' ) ou FORMAT(123456789,'###-##-####') (mais cela ne fonctionne que pour SQL SERVER 2012 And After)

En Cours D'Utilisation Comme UPDATE TABLE_NAME SET COLUMN_NAME = FORMAT(COLUMN_NAME ,'###-##-####')

Et

si votre colonne est Varchar ou Nvarchar utiliser faire comme ceci CONCAT(SUBSTRING(CELLPHONE,0,4),' ',SUBSTRING(CELLPHONE,4,3),'',SUBSTRING(CELLPHONE,7,2) ,' ',SUBSTRING(CELLPHONE,9,2) )

Vous pouvez toujours obtenir de l'aide de

https://msdn.microsoft.com/en-us/library/hh213505.aspx