Navigation

	Home Page
	Index
	All Pages

Contributor Links

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

Other Wiki Sections

Software

Format Phone Numbers - 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