选择只包含大写字母的所有字段

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中只包含大写字符的字段或不包含小写字符的字段?

7 个解决方案

#1

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 COLLATE clause as @kchau suggested in the other answer.

如果不希望对整个表使用区分大小写的排序，也可以使用排序规则子句，如另一个答案中提到的@kchau。

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 COLLATE clause 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)

#2

This worked for me. It found all user emails with uppercase character:

这为我工作。发现所有用户邮件都是大写字母:

SELECT * FROM users WHERE mail REGEXP BINARY '[A-Z]';

#3

select * from table1 where (columnname COLLATE latin1_bin )=upper(depart);

#4

Try this -

试试这个,

select * from <mytable> where upper(<columnname>) = <columnname>

#5

By using REGEXP : http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

通过使用REGEXP: http://www.techrecipes.com/rx/484/use -正则表达式-in-mysql-select-statements/。

Use [:upper:] for uppercase letters.

用[:upper:]表示大写字母。

SELECT * FROM table WHERE field REGEXP '^[[:upper:]+]$'

从表中字段选择* REGEXP”^[[:上:]+]美元”

#6

Basic eg.

基本的如。

SELECT * FROM foo WHERE bar REGEXP '[A-Z]';

从foo中选择*，其中bar REGEXP '[A-Z]';

#7

SELECT column_name FROM table WHERE column_name REGEXP BINARY '^[A-Z]+$'

#1