Link to home
Start Free TrialLog in
Avatar of holyfeld
holyfeld

asked on

Logical ORing SQL LIKES

I've been happily using the following select statement to return values from a table:

SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE  '%OPCServer\SmarOleServer%'  ORDER BY AliasValue

Then someone put a record in the table with an AliasValue containing OPCServer\OACServer, and I'm supposed return this record in addition to the ones above. No problem! I'll just "OR" the LIKES into something resembling

SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE '%OPCServer\SmarOleServer%' OR '%OPCServer\OACServer%' ORDER BY AliasValue

But this returns all the distinct values in the table. So what happens if we group the LIKE into

SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE ('%OPCServer\SmarOleServer%' OR '%OPCServer\OACServer%') ORDER BY AliasValue

This return a null recordset.

Each "LIKE" individually returns the values expected.

I'm OBVIOUSLY missing something in the LIKE wildcarding syntax.

What should the Select statement look like?

Don

ps: I've upped the point count so I can reward someone who can EXPLAIN this, not just the what.
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE '%OPCServer\SmarOleServer%' OR AliasValue LIKE '%OPCServer\OACServer%' ORDER BY AliasValue

AW
Avatar of Guy Hengel [angelIII / a3]
the correct syntax will be this:
SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE '%OPCServer\SmarOleServer%' OR  AliasValue LIKE '%OPCServer\OACServer%' ORDER BY AliasValue

explanation of "why" your syntax fails to work properly:

AliasValue LIKE '%OPCServer\SmarOleServer%' OR '%OPCServer\OACServer%'
will be evaluated like this:
(AliasValue LIKE '%OPCServer\SmarOleServer%' ) OR '%OPCServer\OACServer%'
which will evaluate to either
* (true) or '%OPCServer\OACServer%'
* (false) or '%OPCServer\OACServer%'
and both will actually return true, as the string value is evaluated to true (as long as it is not empty)
hence, it returns ALL the values...


now, the other try of yours should then be self-explaining:
AliasValue LIKE ('%OPCServer\SmarOleServer%' OR '%OPCServer\OACServer%')
will evaluate to AliasValue LIKE (true)
which will return false for all the rows, unless you had a row with true or TRUE as string value in the AliasValue field




ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of holyfeld
holyfeld

ASKER

AW -

Yep. Works like a charm. Now, for all the points, can you explain what the difference is (besides your's works and mine didn't)?

Don
I see AW did post th explanations also meanwhile :-)
Y'all,

Thank you for the answers. Now I can go hiking tomorrow and not worry about this.

I liked Arthur's explanation just a little better, so he got all the points.

Don
sorry about that angelIII, personally I would have split the points 70-30 or maybe 60-40, but that's just me.

AW
AW: no need to be sorry, you deserve the points!
CHeers