选择所有仅包含大写字母的字段 [英] Select ALL fields that contains only UPPERCASE letters
本文介绍了选择所有仅包含大写字母的字段的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何选择mysql中仅包含大写字符的字段或不包含任何小写字符的字段?
How do you select a field that contains only uppercase character in mysql or a field that doesn't contain any lower case character?
推荐答案
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');
然后:
SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)
如果您不想对整个表使用区分大小写的排序规则,则还可以使用 @kchau在其他答案中建议
If you don't want to use a case sensitive collation for the whole table, you can also use the COLLATE
clause as @kchau suggested in the other answer.
让我们尝试使用不区分大小写的排序规则的表:
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');
这不能很好地工作:
SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 1 | SomeThing |
| 2 | something |
| 3 | SOMETHING |
+------+-----------+
3 rows in set (0.00 sec)
但是我们可以使用COLLATE
子句将名称字段整理为区分大小写的整理:
But we can use the COLLATE
clause to collate the name field to a case sensitive collation:
SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$';
+------+-----------+
| id | name |
+------+-----------+
| 3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)
这篇关于选择所有仅包含大写字母的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文