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.
| Author |
Topic |
|
Blessed1978
Yak Posting Veteran
97 Posts |
 Posted - 2014-04-10 : 16:10:15
|
| 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 myCALL LOGS TABLE when it should only be bring back matching results from both table heeeeeeeeeeelllllllllllllppppppp |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 17:02:00
|
| 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 |
 |
|
|
Blessed1978
Yak Posting Veteran
97 Posts |
Posted - 2014-04-10 : 17:54:44
|
| 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. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 17:56:41
|
| So, you are not going to post sample data, is that correct? |
|
|
|
Blessed1978
Yak Posting Veteran
97 Posts |
Posted - 2014-04-10 : 17:58:23
|
| 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 |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 18:21:45
|
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, '(', ''), ')', ''), '-', '') |
|
|
|
|
|
|
|
|
CASE STAMENT HEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPP