Web design and hosting, database, cloud and social media solutions that deliver business results
  • Solução de negócio
    • Automação Robótica de Processos
    • Programas
    • Serviços de banco de dados
      • Relatórios
      • Integração de dados
    • Design de Websites
      • Design de logotipo
      • Gateways de pagamento
      • Localização e Tradução Web
      • Otimização de sites
      • Segurança do site
      • Ferramentas Técnicas
    • Serviços Empresariais
      • Amazon Web Services
      • Serviços do Google Cloud
      • Microsoft Azure
    • Microsoft Office
    • Mídia Social
  • Sobre
    • Carreiras
      • Tradutor Inglês-Espanhol
      • Tradutor Inglês-Turco
      • Tradutor Inglês-Japonês
      • Tradutor Inglês-Português
    • Equipe
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
    • Portfolio
عربى (AR)čeština (CS)Deutsch (DE)English (EN-GB)English (EN-US)Español (ES)فارسی (FA)Français (FR)हिंदी (HI)italiano (IT)日本語 (JA)polski (PL)Português (PT)русский (RU)Türk (TR)中国的 (ZH)

Limpando Números de Telefone no SQL Server

Crie uma função do SQL Server para verificar e limpar uma string de número de telefone internacional ou do Reino Unido

Contexto

denny-muller-7luoHlJdiiU-unsplash.jpg

Um dos desafios mais comuns que enfrentamos na infraestrutura do cliente são os números de telefone formatados incorretamente. Eles tendem a ficar em más condições quando são executados no Excel ou em vários outros cenários, alterando a saída.

Esta função é direcionada a números do Reino Unido, com algum escopo para comprimentos de números internacionais. Ele poderia ser adaptado para qualquer outra região facilmente adaptando os primeiros 6 itens de caso na última declaração de caso e os três itens de caso na segunda declaração de caso.

Ele está em uso há vários anos e recuperou inúmeros números de telefone usando as etapas a seguir;

  • Faça backup do número em uma variável temporária
  • Substitua um sinal de partida por duplo zero
  • Remova todos os caracteres de texto restantes da string
  • Substitua os números de discagem do Reino Unido por 0
  • Verifique o comprimento de cada tipo de número.
  • Retorne o novo número ou retorne o número antigo se não puder ser validado.

SQL

CREATE FUNCTION [dbo].[CleanseTelephone](@TelNo VARCHAR(30))
RETURNS VARCHAR(20)
AS BEGIN
DECLARE @OldNumber VARCHAR(20)=@TelNo

SET @TelNo =(CASE
WHEN LEFT(@TelNo,1)='+' THEN STUFF(@TelNo,1,1,'00')
ELSE @TelNo END)

DECLARE @Letter INT
SET @Letter =PATINDEX('%[^0-9]%',@TelNo)
BEGIN
    WHILE @Letter>0
    BEGIN
    SET @TelNo =STUFF(@TelNo,@Letter,1,'')
    SET @Letter =PATINDEX('%[^0-9]%',@TelNo)
    END
END

SET @TelNo =(CASEWHEN LEFT(@TelNo,5)='00440' THEN STUFF(@TelNo,1,5,'0')WHEN LEFT(@TelNo,4)='0440' THEN STUFF(@TelNo,1,4,'0')
WHEN LEFT(@TelNo,3)='440' THEN STUFF(@TelNo,1,3,'0')
WHEN LEFT(@TelNo,2)='44' THEN STUFF(@TelNo,1,2,'0')
ELSE @TelNo END)

SET @TelNo =(CASE
WHEN LEFT(@TelNo,2)='01' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='02' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='05' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='07' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='09' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='08' AND LEN(@TelNo) BETWEEN 10 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,3)='001' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0020' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0021' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0023' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0024' AND LEN(@TelNo) BETWEEN 11 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0025' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0026' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0027' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0030' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0031' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0032' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0033' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0034' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0035' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0036' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0037' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0038' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0039' AND LEN(@TelNo) BETWEEN 14 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0040' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0041' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0042' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0043' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0044' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0045' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0046' AND LEN(@TelNo) BETWEEN 11 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0047' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0048' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0049' AND LEN(@TelNo) BETWEEN 13 AND 16 THEN @TelNo
WHEN LEFT(@TelNo,4)='0050' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0051' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0052' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0053' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0054' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0055' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0056' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0057' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0058' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0059' AND LEN(@TelNo) BETWEEN 11 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0060' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0061' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0062' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0063' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0064' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0065' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0066' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0067' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0074' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0080' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0081' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0082' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0085' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0086' AND LEN(@TelNo) BETWEEN 15 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0087' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0088' AND LEN(@TelNo) BETWEEN 15 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0090' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0091' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0092' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0093' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0094' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0095' AND LEN(@TelNo) BETWEEN 11 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,4)='0096' AND LEN(@TelNo) BETWEEN 11 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0097' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0098' AND LEN(@TelNo) BETWEEN 11 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,4)='0099' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
ELSE NULL END)

RETURN ISNULL(@TelNo,@OldNumber)
--RETURN @TelNo--Use for returning NULL with non valid numbers
END
GO

Was this helpful?

Please note, this commenting system is still in final testing.

Author

Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

RSSLoginLink Política de CookiesSitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.comClaytabase Ltd, Unit 3d, Rink Road Industrial Estate, PO33 2LT, United Kingdom
As configurações neste site são definidas para permitir todos os cookies. Estes podem ser alterados em nossa página de configurações e políticas de cookie. Ao continuar a usar este site, você concorda com o uso de cookies.
Ousia Logo
Logout
Ousia CMS Loader