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