开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜