CASE STAMENT HEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPP

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 STAMENT HEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPP

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 my
CALL 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, '(', ''), ')', ''), '-', '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources