Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Format Phone Numbers - SQL Server

RSS
Modified on Fri, Jun 23, 2023, 9:18 AM by Administrator Categorized as SQL Server

Code for Scalar Function

create or alter function dbo.FormatPhoneNumber
(
    @PhoneNumber varchar(50)
)
returns varchar(50) as begin
/*-----------------------------------------------------*/
/*
--- TESTING ---
declare @PhoneNumber varchar(30) = '1(800)555-1234'
*/
/*-----------------------------------------------------*/
declare @BadChars varchar(100) = '().-+ '

declare @result varchar(50) = @PhoneNumber

if @PhoneNumber is not null begin

    ;with cte as (
        select * from (
            values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
            ) as c(num)
    )
    ,BadChars as (
        select
            MyChar = substring(@BadChars,num,1)
        from
            cte
        where num <= len(@BadChars)
        )
    select @PhoneNumber = replace(@PhoneNumber, MyChar, '') from BadChars

    if @PhoneNumber like '1%' and len(@PhoneNumber) = 11 begin

        set @result = '1(' 
            + substring(@PhoneNumber, 2, 3)
            + ')'
            + substring(@PhoneNumber, 5, 3)
            + '-'
            + substring(@PhoneNumber, 8, 4)

        end

    else if len(@PhoneNumber) = 10 begin

        set @result = '(' 
            + substring(@PhoneNumber, 1, 3)
            + ')'
            + substring(@PhoneNumber, 4, 3)
            + '-'
            + substring(@PhoneNumber, 7, 4)

        end
 
    end 

return @result

END

SQL Script for Testing

;with cte as (
    select * from (
        values ('1-800-555-1234'),('8005551234'),('+1 (800) 555 - 1234'),('800.555.1234'),
        ('4544-6654-1443')
        ) as c(original)
)
select
    original
    ,formatted = dbo.FormatPhoneNumber(original)
from
    cte

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2023, Patrick Jasinski.