Check for equality on a phone number inputted in a variety of formats

Question

There is a PhoneNumber table which stores the number as the user has input it (e.g. 555 555 5555, 555-555-5555, 555.555.5555)

We register those numbers with a 3-rd party service, which then consumers one our web services, but only provides the number as 10 digits (5555555555).

I need to find a match but filtering out the non-alphanumeric characters in my records.

I added the function that strips out non-alphanumeric characters here: How to strip all non-alphabetic characters from string in SQL Server?

I tried running the following, but I always get 0 records, though I have a 555-555-5555 value for a record in the table:

SELECT COUNT(*)
FROM [dbo].[PhoneNumber]
WHERE [dbo].[RemoveNonAlphaCharacters]([Number]) = '5555555555'

Test the function: `SELECT [dbo].[RemoveNonAlphaCharacters]('555-555-5555')` What does that return? If it's not returning what you expect, you need to adjust the logic of the function. The function you linked is removing non-alpha characters, so if you're using that exact function it won't work. Also if the extent of the variations is periods and dashes, a nested replace is the better option. — Hart CO, Nov 04 '14 at 16:17

Josh Alvo · Answer 1 · 2014-11-04T16:32:01.563

1

SELECT COUNT(*)
FROM [dbo].[PhoneNumber]
WHERE REPLACE([Number],'-','') = '5555555555'

You can use this function several times for the needed characters.

edited Nov 04 '14 at 16:32

answered Nov 04 '14 at 16:17

Josh Alvo

96
1
6

score 1 · Accepted Answer · answered Nov 04 '14 at 16:19

1

You are removing all non alpha characters, including numbers. Try this variant:

Create Function [dbo].[RemoveNonAlphaNumCharacters](@Temp VarChar(8000))
Returns VarChar(800)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-zA-Z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End;

answered Nov 04 '14 at 16:19

Gordon Linoff

1,242,037
58
646
786

For anyone reading this, keep in mind that this doesn't handle the majority of cases as soon as international numbers become involved. For that problem, the best resource I've found so far is the source code of the Android PhoneNumberUtils library: https://android.googlesource.com/platform/frameworks/base/+/master/telephony/java/android/telephony/PhoneNumberUtils.java – Dan Bechard Jun 06 '17 at 14:58

score 1 · Answer 3 · answered Nov 04 '14 at 16:20

1

The function that you are using strips out numeric characters. Everything but a-z. Google for a function that strips out non alpha-numeric characters and use that instead.

answered Nov 04 '14 at 16:20

Tab Alleman

31,483
7
36
52

Check for equality on a phone number inputted in a variety of formats

3 Answers3