Using SQL Server, is there a way to query (555) 344-2524 using 5553442524?

Question

Using the PHP SQLSRV driver to connect to SQL Server 2000, is there a way I could match all of these rows using this piece of data: 5553442524?

555-344-2524
(555) 344-2524
555.344.2524
1-555-344-2524

I imagine this would be done through a specific query probably using a stored procedure?

Thank you.

Answer 1

For SQL 2000 the only way I can think of would be using the REPLACE function.

declare @SearchTerm bigint

Set @SearchTerm = 5553442524

Select * From dbo.Table 
Where Replace(Replace(Replace(Replace(Col1,'-',''), '(',''),')',''),'.','')
= @SearchTerm

The problem with this would be it wouldn't cater for the leading 1.

A better way would be wrap all this logic in to a function.

e.g.

Create Function dbo.fn_FormatTelephoneNumber(@input varchar(100))
returns bigint
as begin


declare @temp bigint

Set @temp = Replace(Replace(Replace(Replace(@input ,'-',''), '(',''),')',''),'.','')

If Len(@temp) = 11
begin
Set @temp = Right(@temp, 10)
end

return @temp

End

To call the function you would use it like so:

Select *,
      dbo.fn_FormatTelephoneNumber(YourColumnName) as [FormattedTelephoneNumber]
From dbo.YourTable

Or to use it in a WHERE clause:

Select *
From dbo.YourTable
Where dbo.fn_FormatTelephoneNumber(YourColumnName) = 5553442524

Obviously the best thing here would be to clean up the data that is stored in the columns and restrict any further "bad" data from being inserted. Although in my experience that is easier said than done.