Atualizando base de dados SQL Server com o nono dígito para DDD 11

Saiba como atualizar, com o nono dígito, registros com o DDD 11 em uma base de dados SQL Server

Após a Anatel regulamentar o nono dígito para telefones com DDD 11, muitos analistas, DBAs, Estágiarios, entre outros, atualizaram suas bases de dados levando em consideração apenas no DDD 11.

Meu caro se você fez isso, sinto muito, mas estava totalmente ERRADO e (como sempre) faltou ler um pouquinho antes de fazer uma alteração crítica.

A Anatel disponibilizou um documento (disponível aqui) que explica como realizar a alteração.

Com base neste documento elaborei um script para atualização de base de dados com telefones com DDD 11.

Eu sei.. poderia ter sido uma procedure, função, aplicação, etc, etc... mas fiz do modo mais simples e rápido!

O script leva em consideração uma tabela que contenha os campos DDD e TELEFONE separados, mas se alguém grava os dois juntos, da pra adaptar e realizar a atualização da mesma forma.

Segue:

BEGIN TRAN

UPDATE tabela_tel
SET    coluna_tel = Cast('9' + Cast(coluna_tel AS VARCHAR) AS INTEGER)
/* Atribui o valor 9 na frente do telefone */
WHERE  Len(coluna_tel) = 8 /* Telefone possui apenas 8 digítos */
       AND coluna_ddd = 11
       /* Inicio regras Anatel */
       AND LEFT(coluna_tel, 1) IN ( 5, 6, 7, 8, 9 )
       AND ( coluna_tel NOT BETWEEN 70000000 AND 70109999
             AND coluna_tel NOT BETWEEN 77000000 AND 78999999
             AND coluna_tel NOT BETWEEN 50109000 AND 50199999
             AND coluna_tel NOT BETWEEN 50210000 AND 50229999
             AND coluna_tel NOT BETWEEN 50290000 AND 50299999
             AND coluna_tel NOT BETWEEN 50310000 AND 50369999
             AND coluna_tel NOT BETWEEN 50410000 AND 50429999
             AND coluna_tel NOT BETWEEN 50440000 AND 50469999
             AND coluna_tel NOT BETWEEN 50490000 AND 50499999
             AND coluna_tel NOT BETWEEN 50510000 AND 50569999
             AND coluna_tel NOT BETWEEN 50580000 AND 50589999
             AND coluna_tel NOT BETWEEN 50600000 AND 50605999
             AND coluna_tel NOT BETWEEN 50610000 AND 50639999
             AND coluna_tel NOT BETWEEN 50650000 AND 50749999
             AND coluna_tel NOT BETWEEN 50770000 AND 50999999
             AND coluna_tel NOT BETWEEN 51020000 AND 51039999
             AND coluna_tel NOT BETWEEN 51050000 AND 51059999
             AND coluna_tel NOT BETWEEN 51100000 AND 51100999
             AND coluna_tel NOT BETWEEN 51110000 AND 51129999
             AND coluna_tel NOT BETWEEN 51150000 AND 51159999
             AND coluna_tel NOT BETWEEN 51710000 AND 51719999
             AND coluna_tel NOT BETWEEN 51800000 AND 51899999
             AND coluna_tel NOT BETWEEN 51910000 AND 51919999
             AND coluna_tel NOT BETWEEN 52120000 AND 52139999
             AND coluna_tel NOT BETWEEN 55010000 AND 55199999
             AND coluna_tel NOT BETWEEN 55210000 AND 55299999
             AND coluna_tel NOT BETWEEN 55310000 AND 55399999
             AND coluna_tel NOT BETWEEN 55410000 AND 55499999
             AND coluna_tel NOT BETWEEN 55530000 AND 55539999
             AND coluna_tel NOT BETWEEN 55600000 AND 55689999
             AND coluna_tel NOT BETWEEN 55710000 AND 55769999
             AND coluna_tel NOT BETWEEN 55790000 AND 55799999
             AND coluna_tel NOT BETWEEN 55810000 AND 55899999
             AND coluna_tel NOT BETWEEN 55910000 AND 55999999
             AND coluna_tel NOT BETWEEN 56010000 AND 56019999
             AND coluna_tel NOT BETWEEN 56030000 AND 56039999
             AND coluna_tel NOT BETWEEN 56110000 AND 56169999
             AND coluna_tel NOT BETWEEN 56200000 AND 56201999
             AND coluna_tel NOT BETWEEN 56210000 AND 56279999
             AND coluna_tel NOT BETWEEN 56310000 AND 56359999
             AND coluna_tel NOT BETWEEN 56410000 AND 56469999
             AND coluna_tel NOT BETWEEN 56600000 AND 56639999
             AND coluna_tel NOT BETWEEN 56650000 AND 56799999
             AND coluna_tel NOT BETWEEN 56810000 AND 56839999
             AND coluna_tel NOT BETWEEN 56850000 AND 56879999
             AND coluna_tel NOT BETWEEN 56910000 AND 56919999
             AND coluna_tel NOT BETWEEN 56930000 AND 56969999
             AND coluna_tel NOT BETWEEN 56980000 AND 56999999
             AND coluna_tel NOT BETWEEN 58110000 AND 58129999
             AND coluna_tel NOT BETWEEN 58140000 AND 58149999
             AND coluna_tel NOT BETWEEN 58160000 AND 58199999
             AND coluna_tel NOT BETWEEN 58210000 AND 58279999
             AND coluna_tel NOT BETWEEN 58310000 AND 58359999
             AND coluna_tel NOT BETWEEN 58370000 AND 58379999
             AND coluna_tel NOT BETWEEN 58390000 AND 58399999
             AND coluna_tel NOT BETWEEN 58410000 AND 58469999
             AND coluna_tel NOT BETWEEN 58510000 AND 58559999
             AND coluna_tel NOT BETWEEN 58700000 AND 58752999
             AND coluna_tel NOT BETWEEN 58759000 AND 58759999
             AND coluna_tel NOT BETWEEN 58900000 AND 58999999
             AND coluna_tel NOT BETWEEN 59040000 AND 59049999
             AND coluna_tel NOT BETWEEN 59060000 AND 59099999
             AND coluna_tel NOT BETWEEN 59200000 AND 59204999
             AND coluna_tel NOT BETWEEN 59208000 AND 59208999
             AND coluna_tel NOT BETWEEN 59210000 AND 59299999
             AND coluna_tel NOT BETWEEN 59310000 AND 59349999
             AND coluna_tel NOT BETWEEN 59380000 AND 59399999
             AND coluna_tel NOT BETWEEN 59700000 AND 59705999
             AND coluna_tel NOT BETWEEN 59710000 AND 59799999 )

COMMIT 

Abraços!

Tweet

Pin It