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