Query help - SQL Server Forums

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)

Query help

Author

Topic

cable_si
Starting Member

20 Posts

Posted - 2013-03-04 : 05:41:16

Hi

Can anyone help improving a query i have

I have a table with a list of phone numbers

Table1

Telephone
01212234567
01212234568
01212234569
01212234544
01212234517
01212234527
01212234537
01212234547
01212234557

Iam given 5 phone numbers and i need to check if each one exists in Table1

At the moment i am doing 5 individual querys

set @existsTel1 = 0
select @existsTel1 = 1 from Table1 where Telephone=@telephone1

I would like to do this in one query, can anybody help at all

thanks

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 05:59:49

so is the prefix part always same?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 11:46:34

If (SELECT count(1) FROM Table WHERE TelephoneNumbers in ('number1','number2',...,'number5'))=5
SET @existsTel1 = 1
ELSE
SET @existsTel1 = 0

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 23:10:30

put those numbers you've in a table and then use a query like


SELECT p.*,
CASE WHEN t.number IS NOT NULL THEN 'Exists' ELSE 'Not EXists' END
FROM PhoneTable p
LEFT JOIN YourTable t
ON p.Telephone LIKE '%' + CAST(t.yournumber AS varchar(5))+ '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources