I'm looking for an SQL query that gives me all rows where ColumnX contains any lowercase letter (e.g. "1234aaaa5789"). Same for uppercase.
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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With