And with or - do I need brackets?

Question

And with or - do I need brackets?

OleHank

SSChasing Mays

Points: 654
More actions
May 12, 2008 at 9:10 am

#185396

If my and & or are for different fields, do I still need brackets? Here is a sample of what I am doing:
SELECT ID
FROM dbo.qryTest
WHERE UPPER(Name) = UPPER('Jim')
AND
UPPER(Addr1) LIKE UPPER('London')
or UPPER(Addr2) LIKE UPPER('New York')
or UPPER(Addr3) LIKE UPPER('Tokyo')
Many thanks for any advice

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you don't use brackets (parentheses), the conditions will be evaluated in the order they appear. So WHERE condition1 AND condition2 OR condition3 OR condition4 is the same as WHERE (condition1 AND condition2) OR condition3 OR condition4.

Hope that helps

John

OleHank SSChasing Mays Points: 654 More actions · Answer 2

thank for your reply John,

I which case I think I need the bracket as I want:

SELECT ID

FROM dbo.qryTest

WHERE UPPER(Name) = UPPER('Jim')

AND

( UPPER(Addr1) LIKE UPPER('London')

or UPPER(Addr2) LIKE UPPER('New York')

or UPPER(Addr3) LIKE UPPER('Tokyo') )

That is return all Jims where one of their addresses match .

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Just a little note you should be using "=" instead of "like" since you are not using any wildcards.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

... an unless you've made the server case sensitive, there's no need for UPPER anywhere in the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OleHank SSChasing Mays Points: 654 More actions · Answer 5

Hello,

thanks for all your advice.

Yes, we work in a case sensitive environement (sigh!)

Using LIKE without a wildcard, doesn't that just make it the same as '=' when comparing strings?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

OleHank (5/12/2008)
Using LIKE without a wildcard, doesn't that just make it the same as '=' when comparing strings?

Yes, it does. If you're looking for values that start with "LONDON", then use LIKE 'LONDON%'. Note that you don't need to use UPPER on the left hand side: just write it in upper case in the first place.

John