Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case format

Author  Topic 

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-15 : 19:26:36
If a have a table with telephone numbers like
lets say 3314565432 and another table has the same number in different formats example 13314565432 b3314565432. How would I write a case statement to lookup 3314565432 even though in the other table it has the same numbers but may have a prefix of 1 or b or any other format so for the table with 3314565432 the entire column name is phone_number. And the table with 13314565432 the column name is lookup_number so basically match any numbers from my phone_number column to the lookup_number column in the target table

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-15 : 20:41:44
[code]SELECT *
FROM TargetTable T
WHERE EXISTS(
SELECT * FROM SourceTable S
WHERE T.lookup_number LIKE '%' + S.phone_number)[/code]or
[code]SELECT *
FROM TargetTable T
WHERE EXISTS(
SELECT * FROM SourceTable S
WHERE RIGHT(T.lookup_number, LEN(S.phone_number)) = S.phone_number)[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page
   

- Advertisement -