CODEWITHSUNDEEP
mysql
user1515503
user1515503

Reputation: 71

mysql queryto list only uppercase row from database

i have one table which consists of 10 columns out of which one column is username . the column username stores the name of student which may be in uppercase and lowercase . i want to segregate the uppercase and lowercase students.if the username consists of any uppercase it will list the row. i am interested in doing query for column username.in other column also uppercase letters are there but i want to list based on username column only. i have tried several query but no one is working.please advice

i want to list rows with any upperletter in column username. i have tried these codes

SELECT * FROM accounts WHERE LOWER(username) LIKE '%q'

did not worked

SELECT * FROM accounts WHERE UPPER(username) = UPPER('%q')

did not worked

SELECT * FROM accounts where username COLLATE latin1_swedish_ci = '%q'

did not worked

SELECT * FROM accounts WHERE username REGEXP '[A-Z]';

did not worked

SELECT * FROM accounts WHERE username REGEXP '^[[:upper:]+]$'

did not worked

Upvotes: 1

Views: 2798

Answers (6)

DeepakGupta
DeepakGupta

Reputation: 19

The following query will work fine

select * from TABLE where CAST( COL_NAME AS BINARY) = upper(COL_NAME);

Upvotes: 0

000
000

Reputation: 3950

CREATE TABLE accounts (
   id int,
   username varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_ci;

SELECT* FROM accounts WHERE username REGEXP '^[A-Z]+$';

Make sure you use COLLATE latin1_general_ci

Upvotes: 1

Max Nachamkin
Max Nachamkin

Reputation: 77

You were on track with the collation, but you need to have a table that is collated, not just the query. What you could do is create a new table, then insert your current rows into the new collated table, then try the REGEX or the rest of the methods.

Select ALL fields that contains only UPPERCASE letters

Upvotes: 0

Noah
Noah

Reputation: 1966

Mysql is case insensitive for strings, so it will be more complecated than a single seletect statement. If you want to do this comparison often, convert the type of the username column to one of the binary types listed below:

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

If you don't want to do this often, consider saving off the results of the current table to a temp table, altering that table with a case sensitive string type, and then using your regex.

Upvotes: -1

mlishn
mlishn

Reputation: 1667

SELECT *          
FROM accounts          
WHERE CAST(username AS BINARY) RLIKE '[A-Z]';

Upvotes: 6

Mike Brant
Mike Brant

Reputation: 71384

First, you need to make sure the field you are searching on have a case-specific collation like latin1_general_cs (if you are using latin character set). Then you can just search for uppercase or lower case, whichever is you are looking for (i.e. WHERE username LIKE '%q%' or WHERE username LIKE '%Q%'

Upvotes: -1

Related Questions