CODEWITHSUNDEEP
sqlsql-server
Mister Epic
Mister Epic

Reputation: 16743

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

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'

Upvotes: 1

Views: 120

Answers (3)

Josh Alvo
Josh Alvo

Reputation: 96

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

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

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

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;

Upvotes: 1

Related Questions