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 STAMENT HEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Yak Posting Veteran

74 Posts

Posted - 04/10/2014 :  16:10:15  Show Profile  Reply with Quote
i WOULD LIKE A CASE Statement that checks to see if a phone number in my current table exists in another table . I am using this query which is not giving me correct results CASE WHEN R.customer_number IN (select R.customer_number from Call_Logs_V2 R where exists (SELECT MobilePhone,HomePhone from [my external table name CUSTOMERS)) THEN '1' ELSE '2' END 'Contact'

The problem with this is that the telephone numbers in my external table is in the format (011) 599-5452 while my CALL LOGS TABLE is in 0115995452 format. I am also having the problem where the above query is bringing back all the #'s in my
CALL LOGS TABLE when it should only be bring back matching results from both table


heeeeeeeeeeelllllllllllllppppppp

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2014 :  17:02:00  Show Profile  Reply with Quote
Can you post sample data and expected output in a consumable format? If you don't know what that means, please see the following links for how to properly post your database questions:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Blessed1978
Yak Posting Veteran

74 Posts

Posted - 04/10/2014 :  17:54:44  Show Profile  Reply with Quote
ok so when i run my case statement CASE WHEN R.customer_number IN (select R.customer_number from Call_Logs_V2 R where exists (SELECT MobilePhone,HomePhone from [my external table name CUSTOMERS)) THEN '1' ELSE '2' END 'Contact'

in my CALL_LOGS_v2 table i have say 155 phone numbers so i would like to see if any of those #'s exists in another table name CUSTOMERS. if it finds a match in both tables it should flag those numbers in my CALL_LOGS_v2 table as 1 if no match then 2.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2014 :  17:56:41  Show Profile  Reply with Quote
So, you are not going to post sample data, is that correct?
Go to Top of Page

Blessed1978
Yak Posting Veteran

74 Posts

Posted - 04/10/2014 :  17:58:23  Show Profile  Reply with Quote
the problem is that the telephone #'s in the CUSTOMERS table is in the format (###)-###-####
and the numbers in my CALL_LOGS_v2 table is in ##########. so i belive i would have to convert the #'s in the CALL_LOGS_v2 to (###)-###-#### in order to match them up. any help would be appreciated.

also not that my case statement is a part of my overall insert into a table
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2014 :  18:21:45  Show Profile  Reply with Quote
Well taking a phone number formatted like (###)-###-#### and removing all the non-numeric characters is quite a bit different from your first post. Your first post is rather convoluted and obviously incorrect on many levels. I wanted sample data so I could show you how to do it better/more properly. But, since you don't see to want to bother, I'll show you how to use the REPALCE function to answer your last question. If you want help fixing your original query, then please post sample data.
DECLARE @Foo VARCHAR(20) = '(409)888-7777';
SELECT REPLACE(REPLACE(REPLACE(@Foo, '(', ''), ')', ''), '-', '')

Edited by - Lamprey on 04/10/2014 18:22:17
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.06 seconds. Powered By: Snitz Forums 2000