Remove and Replace multiple chars ( spaces, hyphen, brackets, period) from string in sql

Question

+39 235 6595750
19874624611
+44 (0)181 446 5697
+431 6078115-2730
+1 617 358 5128 
+48.40.23755432
+44 1691 872 410
07825 893217
0138 988 1649
(415) 706 2001
00 44 (0) 20 7660 4650
(765) 959-1504
07731 508 486
please reply by email
dont have one
+447769146971

Please see the above given phone numbers. I need to replace all spaces, hyphen, period, brackets and leading 0 etc from these numbers. I need this format +447469186974

If number has leading plus sign then don't replace it otherwise I have to concatenate + sign with it.

E.G

+39 235 6595750 in this number I just need to remove spaces.

+44 (0)181 446 5697 in this i need to removes spaces and brackets and 0 in between brackets i.e (0)

07825 893217 in this I need to replace leading 0 with + sign and remove spaces

(415) 706 2001 in this replace '(' with + sign and remove ')' and spaces.

'please reply by email' This is the entry in phone number field and I just need to ignore this.

+48.40.23755432 Remove period in phone number

(765) 959-1504 Remove brackets and spaces and hyphen and add + sign in front of number.

7798724250 just need to add + sign in front of number

00 44 (0) 20 7660-4650 Need to remove leading 0 I.E '00' remove spaces and brackets and 0 in between brackets and hyphen and add + sign in front of number

Only leading '0' will be replaced not anyother occourence of '0'

The desired result is +447769146971

Should I use nested REPLACE, CHARINDES, PATINDES for each char I want to replace?

Edit: I don't have to update these numbers in db. I just want to use these numbers in my query to match these numbers with numbers in call log db. In call log db format is always like this +447769146971

Thanks.

Answer 1

You can potentially use the CLR and Regex for this. This should get you started http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx

Answer 2

give this a try:

SET NOCOUNT ON
DECLARE @Phone table (PhoneNo varchar(50))
INSERT INTO @Phone VALUES ('+39 235 6595750')
INSERT INTO @Phone VALUES ('19874624611')
INSERT INTO @Phone VALUES ('+44 (0)181 446 5697')
INSERT INTO @Phone VALUES ('+431 6078115-2730')
INSERT INTO @Phone VALUES ('+1 617 358 5128 ')
INSERT INTO @Phone VALUES ('+48.40.23755432')
INSERT INTO @Phone VALUES ('+44 1691 872 410')
INSERT INTO @Phone VALUES ('07825 893217')
INSERT INTO @Phone VALUES ('0138 988 1649')
INSERT INTO @Phone VALUES ('(415) 706 2001')
INSERT INTO @Phone VALUES ('00 44 (0) 20 7660 4650')
INSERT INTO @Phone VALUES ('(765) 959-1504')
INSERT INTO @Phone VALUES ('07731 508 486')
INSERT INTO @Phone VALUES ('please reply by email')
INSERT INTO @Phone VALUES ('dont have one')
INSERT INTO @Phone VALUES ('+447769146971')
SET NOCOUNT OFF

;WITH StripNumber AS
(
SELECT
    PhoneNo,
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(PhoneNo
           ,'(','')
           ,')','')
           ,'.','')
           ,' ','')
           ,'-','')
           ,'+','') AS StripNumber
    FROM @Phone
)
SELECT
    CASE
        WHEN ISNUMERIC(StripNumber)=1 THEN '+'+CONVERT(varchar(50),CONVERT(bigint,StripNumber))
        ELSE PhoneNo  --make this ELSE NULL if you don't want to see invalid non numeric phone numbers
    END AS PhoneNumber
    FROM StripNumber

OUTPUT:

PhoneNumber
---------------------------------------------------
+392356595750
+19874624611
+4401814465697
+43160781152730
+16173585128
+484023755432
+441691872410
+7825893217
+1389881649
+4157062001
+4402076604650
+7659591504
+7731508486
please reply by email
dont have one
+447769146971

(16 row(s) affected)

EDIT

based on OP's latest edit: I don't have to update these numbers in db. I just want to use these numbers in my query to match these numbers... why on earth would you want to format the number each time you run your query?? store the formatted numbers in the DB and then just join on them. Even if you just make a PERSISTED computed column or an view with an indexed on this formatted number, you'd have much better performance.

Answer 3

One solution would be to Create a function

 Create Function ExtractDigits( @inVal varChar(50), @EliminateLeadingZeroes TinyInt)
 Returns VarChar(50)
 As
 Begin
     Declare @outVal VarChar(50)
     Set @outVal = ''
     Declare @C Char(1)
     While Len(@Inval) > 0 Begin
         Set @C = Left(@InVal, 1)
         Set @InVal = SubString(@InVal, 2, Len(@InVal) -1)
         If @C Between '0' And '9'
             Set @outVal = @outVal + @C
     End
     If @EliminateLeadingZeroes = 1
        While Left(@outVal,1) = '0'
             Set @OutVal = Substring(@OutVal,2,Len(OitVal) -1)
     Return @OutVal
End

Then write Update Statement like this:

While Exists (Select * From table 
              Where Phone Not Like'+[0-9]%'
Update table Set 
    Phone = '+' + dbo.ExtractDigits(Phone, 1)
Where Phone Not Like'+[0-9]%'