Separate ddd from phone with SQL statement

Question

I have a table with fields DDD and Phone. Some were registered correctly, others the ddd is next to the phone and need to separate.

TABLE

+--------------------+
|   DDD  | Telefone  |  
+--------------------+     
|   47   | 89876543  | ---> ok
+--------------------+    
|  NULL  |4799843838 | ---> ajustar
+--------------------+    
|  NULL  |04799843838| ---> ajustar
+--------------------+    

How could I update my database using SQL statement to put the DDD in the correct field, because it is next to the phone, only it should enter the NULL value. Considering also that have to remove the 0 on the left (when)?

Answer 1

You can remove all spaces and words NULL, if you don’t have a 0 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 SQL output will be something like:

[phone]
[phone]
[phone]

Answer 2

You can be doing this way :

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','','[phone]','A',''),
(7,7,'C','','(051)988765439 - ramal 3245','A',''),
(8,7,'C','','[phone] - 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 the case and the SUBSTRING to remove the parts you need from the string.

Answer 3

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

Answer 4

Basically you can use 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 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 computer, answering by cell phone!

Answer 5

In my case I had 2 problems

• Identify if it was a cell phone number
• Refresh by typing 9.

Update Cell Phone Type

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

Clear the formwork (old only)

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

Formater adding 9 (old 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 phone numbers that start with 6,7,8,9 are mobile numbers.