Format Phone Numbers With Characters

Question

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.

It is not clear from your question if you want dashes in your results or not (the example says you don't but then the description beneath it says you do). Please disambiguate this. Also please let us know how non-US phone numbers should be treated. It would also be good to see the SQL you have tried so far so we can show where you are going wrong. — Mike Poole, Jul 12 '19 at 06:44
This may help you https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Hardik Leuwa, Jul 12 '19 at 06:50

Mubinjon Muinov · Answer 1 · 2019-07-12T08:37:18.950

2

ALTER FUNCTION [dbo].[GetPhone]
(@strAlphaNumeric VARCHAR(256))
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @SourceStr NVARCHAR(10);
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
if(LEFT(@strAlphaNumeric,1)='1') begin
  SET @SourceStr =RIGHT(CONVERT(NVARCHAR(11), @strAlphaNumeric),10);
end else begin
  SET @SourceStr =CONVERT(NVARCHAR(10), @strAlphaNumeric);
end
RETURN @SourceStr;
END

edited Jul 12 '19 at 08:37

answered Jul 12 '19 at 07:12

Mubinjon Muinov

51
8

thank you for this. The problem with this is it always omits the first number and still get 10 digits. I should only be getting rid of first number if it is 1 and the whole digit is 11. So with this code, for instance, 856-234-0258 ext 2710 will return 5623402582. As you can see, it included '2' from 2710. – Mary Queen Amora Jul 12 '19 at 07:32
I think you mean IF(LEFT(@strAlphaNumeric,1)='1') instead of if(LEFT(@SourceStr,1)='1') – Mary Queen Amora Jul 12 '19 at 07:50

Yeou · Accepted Answer · 2019-07-12T08:38:46.420

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.

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

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

Thank you for this solution. Though, it is not omitting the country code. I should be getting rid of '1' country code if the digit length is greater than 10 and starts with 1 — Mary Queen Amora, Jul 12 '19 at 08:02
@MaryQueenAmora you can just do a simple query on the above result. — Yeou, Jul 12 '19 at 08:39

score 1 · Answer 3 · answered Jul 12 '19 at 06:46

1

Just use nested replaces:

select replace('.', '', 
       replace('=', '', 
       replace(')', '', 
       replace('(', '', 
       replace(',', '',
       phoneNumber)))))
from MyTable

answered Jul 12 '19 at 06:46

Michał Turczyn

32,028
14
47
69

Format Phone Numbers With Characters

3 Answers3