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.
SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE '%OPCServer\SmarOleServer%
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%
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
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.
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
SELECT DISTINCT AliasValue FROM GAS_Aliases2ThemeItems1 WHERE AliasValue LIKE '%OPCServer\SmarOleServer%
explanation of "why" your syntax fails to work properly:
AliasValue LIKE '%OPCServer\SmarOleServer%
will be evaluated like this:
(AliasValue LIKE '%OPCServer\SmarOleServer%
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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 :-)
ASKER
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
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
AW: no need to be sorry, you deserve the points!
CHeers
CHeers
AW