CODEWITHSUNDEEP
sqlsql-server
Mary Queen Amora
Mary Queen Amora

Reputation: 33

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.

Upvotes: 0

Views: 1367

Answers (3)

Yeou
Yeou

Reputation: 632

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
  1. 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

Upvotes: 2

Mubinjon Muinov
Mubinjon Muinov

Reputation: 51

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

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

Just use nested replaces:

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

Upvotes: 1

Related Questions