Format Phone Numbers With Characters

I need to format the existing phone numbers saved in my database. First, I need to clean the phone numbers with character.

801-113-1521

801.456.7891

1213-345-6789 ext 219

(323)567=2251

should be:

8011131521

8014567891

12133456789

3235672251

After, I will format it to (3 digit area code) ###-#### Then disregard the ‘1’ country code for 11 digit numbers

I have tried

DECLARE @string varchar(100)

SET @string = phoneNumber

-- loop till there are characters left in a string
WHILE PATINDEX('%[^0-9]%',@string) <> 0
BEGIN
    -- replace characters with empty space
    SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'')
END
SELECT @string

but when it comes to 1213-345-6789 ext 219, it includes 219. I am new to mssql.

Answer

You can try creating one more function to clean up any trailing number that comes after alphabets.

First, clean up any special character. Second, clean trailing numbers and alphabets.

1. Function to remove Special Characters

CREATE Function [dbo].[RemoveSpecialCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9A-Za-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

2. Function to remove trailing numbers and alphabets

CREATE Function [dbo].[RemoveExtraCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        -- Here you specify the length of text that you need to remove len(@Temp)-PatIndex(@KeepValues, @Temp)+1
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), len(@Temp)-PatIndex(@KeepValues, @Temp)+1, '')

    Return @Temp
End

Then you can call function on your column.

SELECT [dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)) FROM Yourtable

Edit:

This will remove the country code.

SELECT CASE WHEN
    LEN([dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)))>10 THEN 
        RIGHT([dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)), 10)
    ELSE 
        [dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo))
    END AS PhoneNo
FROM Yourtable

You can see example here