Separate ddd from phone with SQL statement

You can remove all spaces and words NULL , if you do not have a 0 on the left, add one, then format the string:

DECLARE @Telefones AS TABLE (
    [DDD Telefone] VARCHAR(50)
)

INSERT INTO @Telefones VALUES ('47 89876543')
INSERT INTO @Telefones VALUES ('NULL 4799843838')
INSERT INTO @Telefones VALUES ('NULL 04799843838');

WITH CTE_Telefones AS (
    SELECT REPLACE(REPLACE([DDD Telefone], 'NULL', ''), ' ', '') AS  [DDD Telefone]
    FROM @Telefones
), CTE_Telefones_Zero AS (
    SELECT 
        CAST(CASE CHARINDEX('0', [DDD Telefone])
            WHEN 1 THEN [DDD Telefone] 
            ELSE '0' + [DDD Telefone]
        END AS VARCHAR(MAX)) as [DDD Telefone]
    FROM CTE_Telefones
)

SELECT 
    '(' + SUBSTRING([DDD Telefone], 1, 3) + ') ' + 
    SUBSTRING([DDD Telefone], 3, LEN([DDD Telefone]) - 7) + '-' + 
    SUBSTRING([DDD Telefone], LEN([DDD Telefone]) - 3, 4) AS [DDD Telefone]
FROM CTE_Telefones_Zero

The output of SQL will be something like this:

(047) 7898-6543
(047) 7998-3838
(047) 7998-3838

You can do this by doing this:

declare @contatos table
(
    ID_CONTATO_TELEFONE int,
    ID_CONTATO int,
    TIPO_TELEFONE varchar(1),
    DDD varchar(5),
    NUMERO varchar(100),
    IND_SITUACAO varchar(1),
    OBSERVACAO varchar(100)
)

insert into @contatos
values(1,1,'F','011','98876667','A',''),
(2,2,'F','','1198876667','A',''),
(3,3,'C','','01198876667','A',''),
(4,4,'C','','08798687667','A',''),
(5,5,'C','','(51)988765473','A',''),
(6,6,'C','','(051)98876543','A',''),
(7,7,'C','','(051)988765439 - ramal 3245','A',''),
(8,7,'C','','(051)88765439 - ramal 3245','A','');





select ID_CONTATO_TELEFONE,ID_CONTATO, TIPO_TELEFONE, 
case when DDD is not null and DDD <> '' then DDD 
     else case when SUBSTRING(NUMERO,1,2) = '(0' then SUBSTRING(NUMERO,2,3) 
               when SUBSTRING(NUMERO,1,1) = '(' then ('0' +SUBSTRING(NUMERO,2,2))
               when SUBSTRING(NUMERO,1,1) = '0' then SUBSTRING(NUMERO,1,3) 
               else  ('0' +SUBSTRING(NUMERO,1,2)) 
        end 
end as DDD,
case when DDD is not null and DDD <> '' then NUMERO
     else case when SUBSTRING(NUMERO,1,2) = '(0' then   case  when ISNUMERIC(SUBSTRING(NUMERO,6,9)) = 1 then SUBSTRING(NUMERO,6,9) else SUBSTRING(NUMERO,6,8) end
                   when SUBSTRING(NUMERO,1,1) = '(' then  case  when ISNUMERIC(SUBSTRING(NUMERO,5,9)) = 1 then SUBSTRING(NUMERO,5,9) else SUBSTRING(NUMERO,5,8) end
                   when  SUBSTRING(NUMERO,1,1) = 0 then  case  when ISNUMERIC(SUBSTRING(NUMERO,4,9)) = 1 then SUBSTRING(NUMERO,4,9) else SUBSTRING(NUMERO,4,8) end 
                   else NUMERO
            end  
end as NUMERO,
IND_SITUACAO, OBSERVACAO from @contatos;

Use case and SUBSTRING to remove the parts you need from string .

Basically you can use the SubString:

UPDATE nome_tabela SET
   DDD = SUBSTRING(Telefone FROM 1 FOR 2)
  ,Telefone = SUBSTRING(Telefone FROM 3 FOR 9) 
 WHERE CAST(SUBSTRING(Telefone FROM 1 FOR 2) AS INTEGER) = 47;

Here testing whether it starts with 0:

UPDATE nome_tabela SET
   DDD = SUBSTRING(Telefone FROM 2 FOR 3)
  ,Telefone = SUBSTRING(Telefone FROM 4 FOR 10) 
 WHERE CAST(SUBSTRING(Telefone FROM 1 FOR 1) AS INTEGER) = 0;

Do the tests, I'm without pc, answering the cell phone!

select
 left( convert(bigint,replace( replace([Telefone],' ',''),'NULL','')),2) as DDD,
 Substring( convert(varchar(255),convert(bigint,replace( replace([Telefone],' ',''),'NULL',''))),3,11) as Telefone
 from Telefone

In my case I had 2 problems

• Identify if it was a mobile number
• Update by entering the digit 9.

Update Mobile Type

UPDATE Telefone SET descricao = "Celular" 
WHERE numero LIKE "(%" AND SUBSTRING(numero,5,1) IN ('6', '7', '8', '9');

Clear formatting (old ones only)

UPDATE Telefone SET numero = REPLACE(REPLACE(REPLACE(numero,'(',''),'-',''),')','') 
WHERE descricao = "Celular" AND LENGTH(numero) = 13;

Formater by adding the 9 (old ones only - lenth = 10)

UPDATE Telefone SET numero = CONCAT('(',SUBSTRING(numero,1,2), ')9', SUBSTRING(numero, 3, 4), '-', SUBSTRING(numero, 7, 4)) 
WHERE descricao = "Celular" AND LENGTH(numero) = 10;

  

Currently by anatel only the telephone numbers that start with   6,7,8,9 are cell numbers.