MySQL 选择仅包含大写字母的所有字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3836825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select ALL fields that contains only UPPERCASE letters
提问by Aivan Monceller
How do you select a field that contains only uppercase character in mysql or a field that doesn't contain any lower case character?
在mysql中如何选择只包含大写字符的字段或不包含任何小写字符的字段？
采纳答案by Daniel Vassallo
You may want to use a case sensitive collation. I believe the default is case insensitive. Example:
您可能希望使用区分大小写的排序规则。我相信默认值不区分大小写。例子：
CREATE TABLE my_table (
id int,
name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_cs;
INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');
Then:
然后：
SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)
If you don't want to use a case sensitive collation for the whole table, you can also use the COLLATEclause as @kchau suggested in the other answer.
如果您不想对整个表使用区分大小写的排序规则，您还可以使用其他答案中@kchau 建议的COLLATE子句。
Let's try with a table using a case insensitive collation:
让我们尝试使用不区分大小写的排序规则的表：
CREATE TABLE my_table (
id int,
name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_ci;
INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');
This won't work very well:
这不会很好地工作：
SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 1 | SomeThing |
| 2 | something |
| 3 | SOMETHING |
+------+-----------+
3 rows in set (0.00 sec)
But we can use the COLLATEclause to collate the name field to a case sensitive collation:
但是我们可以使用COLLATE子句将 name 字段整理为区分大小写的整理：
SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)
回答by Jasom Dotnet
This worked for me. It found all user emails with uppercase character:
这对我有用。它找到了所有带有大写字符的用户电子邮件：
SELECT * FROM users WHERE mail REGEXP BINARY '[A-Z]';
回答by kafuchau
SELECT * FROM table1 WHERE (columnname COLLATE latin1_bin )=UPPER(depart);
回答by Sachin Shanbhag
Try this -
尝试这个 -
SELECT * FROM <mytable> WHERE UPPER(<columnname>) = <columnname>
回答by Ruel
By using REGEXP: http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
通过使用REGEXP：http: //www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
Use [:upper:]for uppercase letters.
使用[:upper:]了大写字母。
SELECT * FROM table WHERE field REGEXP '^[[:upper:]+]$'
回答by NAGARJUNA PALURU
SELECT column_name FROM table WHERE column_name REGEXP BINARY '^[A-Z]+$'
回答by commonpike
Found this in the comments - it deserves a post of its own:
在评论中发现了这一点 - 它应该有自己的帖子：
SELECT * FROM mytable WHERE BINARY mycolumn = BINARY UPPER(mycolumn);
The problem with WHERE UPPER(mycolumn) = mycolumnis the collation, and it depends on your table what you can use there.
问题WHERE UPPER(mycolumn) = mycolumn在于排序规则，这取决于您的表可以在那里使用什么。
