As I was looking at my notes I came across a little T-SQL script that made it easy to perform a SELECT to get an ID based on a set of input criteria, and a phone number.  As you may be aware - phone numbers are often formatted differently with extra characters such as spaces, hyphens, open and closed parens, etc.  
So using the combination of a CASE statement, and some nested REPLACE functions I was able to remove:
- spaces
- hyphens
- open paren (
- closed paren )
DECLARE @MyVar as nvarchar(50)
SET @MyVar = '(777) 777-7777 '
SELECT TOP 1 [id] FROM [MyTable] 
WHERE 1 = (
    CASE WHEN 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE([Phone],'-','')
            ,')','')
        ,'(','')
    ,' ','')=@MyVar 
   THEN 1 
   ELSE 0 
   END)
Share This To :
 
0 comments:
Post a Comment