MYSQL problem-> WHERE ... OR
Thanks to some help on this site, I've written a query to find any rows WHERE 'first' or 'last' are not capitalized. Each condition works fine on its own, but when combined into a single query with OR, then I no longer detect non-capitalized entries in the 'first' column - only in the 'last' col开发者_运维技巧umn. Where am I going wrong? Thanks.
SELECT first,last FROM main WHERE
CONCAT( UPPER( SUBSTRING(first,1,1) ), SUBSTRING(first FROM 2) ) != first
OR
CONCAT( UPPER( SUBSTRING(last,1,1) ), SUBSTRING(last FROM 2) ) != last
COLLATE latin1_general_cs
You need the COLLATE on both conditions:
SELECT first,last FROM main WHERE
CONCAT( UPPER( SUBSTRING(first,1,1) ), SUBSTRING(first FROM 2) ) != first
COLLATE latin1_general_cs
OR
CONCAT( UPPER( SUBSTRING(last,1,1) ), SUBSTRING(last FROM 2) ) != last
COLLATE latin1_general_cs
Or, simplifying the query per the suggestion in the comments above:
SELECT first,last FROM main WHERE
UPPER( SUBSTRING(first,1,1)) != SUBSTRING(first,1,1)
COLLATE latin1_general_cs
OR
UPPER( SUBSTRING(last,1,1)) != SUBSTRING(last,1,1)
COLLATE latin1_general_cs
Try making the order of evaluation of the WHERE clause more explicit by adding additional parentheses...
SELECT first,last FROM main WHERE
(CONCAT( UPPER( SUBSTRING(first,1,1) ), SUBSTRING(first FROM 2) ) != first)
OR
(CONCAT( UPPER( SUBSTRING(last,1,1) ), SUBSTRING(last FROM 2) ) != last)
COLLATE latin1_general_cs
精彩评论