How do you select a field that contains only uppercase character in mysql or a field that doesn't contain any lower case character?
1
-
2Are the fields allowed to contain non-letters?martin clayton– martin clayton2010-10-01 06:01:03 +00:00Commented Oct 1, 2010 at 6:01
Add a comment
|
8 Answers
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.
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:
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 Comments
Aivan Monceller
another way is to use BINARY compare BINARY city_name = BINARY upper(city_name)
northtree
@AivanMonceller BINARY is the better answer in fact :)
This worked for me. It found all user emails with uppercase character:
SELECT * FROM users WHERE mail REGEXP BINARY '[A-Z]';
1 Comment
geg
"REGEXP is not case sensitive, except when used with binary strings." -- dev.mysql.com/doc/refman/5.7/en/regexp.html
SELECT * FROM table1 WHERE (columnname COLLATE latin1_bin )=UPPER(depart);
1 Comment
Daniel Vassallo
That will also select fields that contain numbers and other characters. I don't think the OP wants that.
By using REGEXP : http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
Use [:upper:] for uppercase letters.
SELECT * FROM table WHERE field REGEXP '^[[:upper:]+]$'
1 Comment
Aivan Monceller
using [:upper:] would also select 'Example' .. [^a-z] doesn't seem to work fine
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) = mycolumn is the collation, and it depends on your table what you can use there.
Comments
SELECT column_name FROM table WHERE column_name REGEXP BINARY '^[A-Z]+$'
Comments
Basic eg.
SELECT * FROM foo WHERE bar REGEXP '[A-Z]';
2 Comments
Aivan Monceller
not working thanks SELECT 'as' REGEXP '[A-Z]' returns 1 SELECT 'AS' REGEXP '[A-Z]' returns 1
kafuchau
Can your field contain other characters, such as spaces, etc? If so, you would want to include those in the regular expression pattern as well.
Try this -
SELECT * FROM <mytable> WHERE UPPER(<columnname>) = <columnname>
2 Comments
Daniel Vassallo
That will not work with a case insensitive collation. In addition, that will also select fields that contain numbers and other characters. I don't think the OP wants that.
Sachin Shanbhag
@Daniel - Yes, I agree this does not handle special characters. But the question is not clear about he wants or does not want to retain special characters.