SQL Query to clean up phone numbers

I have a varchar(100) field in SQL Server 2008 with phone numbers.  There is no set format, some are 1231234567, some are (123)123-4567, some are 123.123.4567, and various other iterations.

I need a query to just find the 10 numbers in the field (and ignore spaces and special charcters) and update them like this 123-123-4567.
telecommandoAsked:
Who is Participating?
 
Lee SavidgeConnect With a Mentor Commented:
I use the function below.

Lee
if exists (select * from sysobjects where name = N'CleanPhoneNumber' and type = N'FN')
    drop function CleanPhoneNumber
go

create function CleanPhoneNumber(@vchPhoneNumber as nvarchar(255))
returns nvarchar(255)
as
begin
    declare @vchCleanedPhoneNumber nvarchar(255)

    if @vchPhoneNumber is not null
    begin
        declare @vchCurrentDigit       nvarchar(1)
        declare @iCount                int

        select @vchCleanedPhoneNumber = ''
        select @vchPhoneNumber = replace(@vchPhoneNumber, ' ', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '+', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '-', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '£', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '$', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '€', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, ',', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '.', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, '(', '')
        select @vchPhoneNumber = replace(@vchPhoneNumber, ')', '')

        select @iCount = 1

        if left(@vchPhoneNUmber, 2) = '44'
            select @vchPhoneNumber = right(@vchPhoneNumber, len(@vchPhoneNumber) - 2)

        if left(@vchPhoneNumber, 1) = '0'
            select @vchPhoneNumber = right(@vchPhoneNumber, len(@vchPhoneNumber) - 1)

        while @iCount <= len(@vchPhoneNumber)
        begin
            select @vchCurrentDigit = substring(@vchPhoneNumber, @iCount, 1)
            if isnumeric(@vchCurrentDigit) = 1
                select @vchCleanedPhoneNumber = @vchCleanedPhoneNumber + @vchCurrentDigit
            select @iCount = @iCount + 1
        end
    end
    return @vchCleanedPhoneNumber
end
go

Open in new window

0
 
Lee SavidgeCommented:
You'd need to edit it slightly to return the format you want.

Change this:

return @vchCleanedPhoneNumber

to

return left(@vchCleanedPhoneNumber, 3) + '-' + left(right(@vchCleanedPhoneNumber, 6), 3) + '-' + right(@vchCleanedPhoneNumber, 4)

This will only work if the number is always 10 digits.

Lee


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.