doudong1117
2013-06-20 15:11
浏览 25
已采纳

使用SQL Server,有没有办法使用5553442524查询(555)344-2524?

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.

图片转代码服务由CSDN问答提供 功能建议

使用PHP SQLSRV驱动程序连接到SQL Server 2000,有没有办法可以匹配所有这些行使用 这条数据: 5553442524

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

我想这可以通过特定的查询来完成,可能使用存储过程?

谢谢。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • duanpa1980 2013-06-20 15:15
    最佳回答

    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题