Блог

У меня есть поле номера телефона в базе данных. В нем уже есть данные.

Я хочу изменить формат своего телефонного номера на «XXX-XXX-XXXX», в текущей базе данных нет формата телефона.

Таким образом, могут быть данные мусора. Я уже применил проверку для новых записей, но теперь я хочу также изменить свои существующие данные.

Есть ли какой-либо конкретный способ, с помощью которого я могу изменить свои существующие данные. И сделайте так, чтобы все телефонные номера соответствовали этому формату.

Пожалуйста, совет.

Создайте функцию для удаления нечисловых данных и форматирования

 CREATE FUNCTION [UDF_STRIP_NONNUMERIC_DATA](@str VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
  BEGIN
      WHILE Patindex('%[^0-9]%', @str) > 0
        BEGIN
            SET @str = Stuff(@str, Patindex('%[^0-9]%', @str), 1, '')
        END

      RETURN @str
  END 
  

Вы можете использовать STUFF функцию для вставки - между phone номерами

 Select left(Stuff(Stuff(dbo.[UDF_STRIP_NONNUMERIC_DATA](Phone),4,0,'-'),8,0,'-'),12)
From yourtable
  

Если вы используете SQL SERVER 2012 функцию use can use FORMAT (спасибо LukStorms, который упомянул ее в комментарии)

 SELECT Format(Cast(dbo.[Udf_strip_nonnumeric_data](Phone) AS BIGINT), '###-###-####')
FROM   yourtable 
  

Для обновления

 Update yourtable 
   SET phone = left(Stuff(Stuff(dbo.[UDF_STRIP_NONNUMERIC_DATA](Phone),4,0,'-'),8,0,'-'),12)
  

ДЕМОНСТРАЦИЯ

 declare @str  varchar(100)= '9225-123-4567'

select left(Stuff(Stuff(dbo.[UDF_STRIP_NONNUMERIC_DATA](@str),4,0,'-'),8,0,'-'),12)
  

Результат : 922-512-3456

 declare @phone varchar(24)
set @phone = '(334)789-4532'
--set @phone = '314789-4532'
--set @phone = '3457894532'
--set @phone = '534-789-4532'

SELECT
    LEFT(N,3)   '-'   SUBSTRING(N,4,3)   '-'   RIGHT(N,4)
FROM
    (SELECT CAST(CAST((
            SELECT SUBSTRING(@phone, Number, 1)
            FROM master..spt_values
            WHERE Type='p' AND Number <= LEN(@phone) AND
                SUBSTRING(@phone, Number, 1) LIKE '[0-9]' FOR XML Path(''))
        AS xml) AS varchar(MAX)) as N) as N
  

Хорошо, чтобы заменить все нечисловые символы, посмотрите на это.

Вот пример сценария (скопированный по этой ссылке), чтобы показать вам, как это работает (вам нужно будет изменить это, чтобы соответствовать вашему имени таблицы и имен столбцов:

 -- Step 1: creates table to use to hold every char in every phone number
if object_id('dbo.tally') is not null drop table dbo.tally   
   select top 10000 --change to fit max length of phone number
     identity(int,1,1) as n
   into dbo.tally
   from master.dbo.syscolumns sc1,
        master.dbo.syscolumns sc2

-- add pk to maximize performance
  alter table dbo.tally
     add constraint pk_tally_n 
        primary key clustered (n) with fillfactor = 100

-- Step 2: Create temporary table holding three bad phone numbers  
 declare @phonetable table 
   (uniqueid int identity(1,1), 
   phone_number varchar(500)) 
   insert into @phonetable (phone_number) 
   select '01234-567-890' union 
   select '012345 6789ext' union 
   select 'n/a' union select '...12345.....';

 -- Step 3: identify, for every character, whether it is a number or not,
           and remove the non-numeric ones
 with cte (uniqueid, phone_number, goodchar, badchar) as
 ( select uniqueid, phone_number, 
        case when substring(phone_number,N,1) not like '%[^0-9]%' 
             then substring(phone_number,N,1) end as goodchar, 
        case when substring(phone_number,N,1) like '%[^0-9]%' 
             then substring(phone_number,N,1) end as badchar
   from @phonetable , Tally  
   where phone_number like '%[^0-9]%' and N <= len(phone_number) )
   select distinct phone_number, 
        isnull( stuff ( 
                  ( SELECT ''   goodchar 
                FROM cte t1 
        where t1.UniqueID = t2.UniqueID 
          FOR XML PATH ( '' ) ) , 1 , 0 , '' ) ,'')  
                        as clean_phone_number from cte t2 
  

чтобы отобразить номера с форматированием, просто извлеките соответствующие фрагменты и повторно объедините их с тире.

 Select case len(phone) 
       When 10  then left(phone, 3)   '-'   
                     substring(phone, 4,3)   '-'   
                     substring(phone, 7,4)`
       When 7 then left(phone, 3)   '-'   
                   substring(phone, 4,4)
       Else '' end
  

Для создания вычисляемого столбца

 Alter table Add Column FormattedPhone as 
  case len(phone) 
       When 10  then left(phone, 3)   '-'   
                     substring(phone, 4,3)   '-'   
                     substring(phone, 7,4)`
       When 7 then left(phone, 3)   '-'   
                   substring(phone, 4,4)
       Else '' end
  

Если вы не возражаете против UDF

 Select [dbo].[udf-Str-Format-Phone]('334)789-4532')  
  

ВОЗВРАТ

 334-789-4532
  

UDF

 CREATE FUNCTION [dbo].[udf-Str-Format-Phone] (@S varchar(max))
Returns varchar(25)
AS
Begin
    Declare @Return varchar(25)

    ;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1)) N(N))
        , cteN(N) As (Select Top (Len(@S)) Row_Number() over (Order By (Select NULL)) From cte0 N1, cte0 N2)
        , cteS(S) As (Select Substring(@S,N,1) From cteN Where Substring(@S, N, 1) LIKE '[0-9]' FOR XML Path(''))

    Select @Return = IIf(Len(S)>=10,Stuff(stuff(S,4,0,'-'),8,0,'-'),Stuff(S,4,0,'-')) From cteS
    Return @Return

End
-- Syntax : Select [dbo].[udf-Str-Format-Phone]('(334)789-4532')  -- Returns 334-789-4532
-- Syntax : Select [dbo].[udf-Str-Format-Phone]('Phone:7894532')  -- Returns 789-4532