James Ray Anderson

James Ray Anderson
James Ray Anderson
0 comments

T-SQL - Selecting a record based on a phone number

4:58 PM
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)

0 comments:

 
Toggle Footer
Top