Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find rows that have a value that contains a lowercase letter

Tags:

sql

mysql

I'm looking for an SQL query that gives me all rows where ColumnX contains any lowercase letter (e.g. "1234aaaa5789"). Same for uppercase.

like image 804
ripper234 Avatar asked Nov 08 '10 11:11

ripper234


People also ask

How do I find lowercase letters in SQL?

The SQL LOWER function converts all the characters in a string into lowercase. If you want to convert all characters in a string into uppercase, you should use the UPPER function. The following illustrates the syntax of the LOWER function. The LOWER function returns a string with all characters in the lowercase format.

How do you find lowercase and uppercase in SQL?

Case insensitive SQL SELECT: Use upper or lower functionsselect * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.

How do you find lowercase letters?

The islower() method returns True if all alphabets in a string are lowercase alphabets. If the string contains at least one uppercase alphabet, it returns False.

How will you check in a string that all characters are in lowercase?

Traverse the string character by character from start to end. Check the ASCII value of each character for the following conditions: If the ASCII value lies in the range of [65, 90], then it is an uppercase letter. If the ASCII value lies in the range of [97, 122], then it is a lowercase letter.


2 Answers

SELECT * FROM my_table WHERE my_column = 'my string' COLLATE Latin1_General_CS_AS 

This would make a case sensitive search.


EDIT

As stated in kouton's comment here and tormuto's comment here whosoever faces problem with the below collation

COLLATE Latin1_General_CS_AS 

should first check the default collation for their SQL server, their respective database and the column in question; and pass in the default collation with the query expression. List of collations can be found here.

like image 45
Devraj Gadhavi Avatar answered Sep 28 '22 18:09

Devraj Gadhavi


SELECT * FROM my_table  WHERE UPPER(some_field) != some_field 

This should work with funny characters like åäöøüæï. You might need to use a language-specific utf-8 collation for the table.

like image 133
geon Avatar answered Sep 28 '22 20:09

geon