SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Yak Posting Veteran

59 Posts

Posted - 04/15/2014 :  19:26:36  Show Profile  Reply with Quote
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

Japan
65 Posts

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


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000