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.
使用PHP SQLSRV驱动程序连接到SQL Server 2000，有没有办法可以匹配所有这些行使用 这条数据： 5553442524 ？
我想这可以通过特定的查询来完成，可能使用存储过程？
谢谢。
555-344-2524
（555）344-2524
555.344.2524
1-555-344-2524
\ n
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.