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

sql

case-sensitivity

collation

regex

byAnton Shumikhin·Oct 21, 2024

In SQL, find rows with lowercase by using the LIKE pattern ('%[a-z]%'). For regex-friendly databases, utilize REGEXP with the '[a-z]' pattern.

SQL with LIKE:

-- Get me those lowercase goodies!
SELECT * FROM table WHERE column LIKE '%[a-z]%';

SQL with REGEXP:

-- Regex to the rescue!
SELECT * FROM table WHERE column REGEXP '[a-z]';

For a case-sensitive comparison, let's pit column values against their uppercase alter egos (UPPER(column)).

-- In a case "sensitivity training"
SELECT * FROM table WHERE UPPER(column) != column;

Case sensitivity: How to make SQL respect differences

Case sensitivity: The power of collation

Ensure case sensitivity even when your database’s collation thinks "Upper" and "lower" are the same thing. Just opt for COLLATE with a case-sensitive collation.

SQL Server:

-- Because being CASE sensitive matters! 
SELECT * FROM table WHERE column COLLATE Latin1_General_CS_AS LIKE '%[a-z]%';

Binary comparison: The road less taken

In MySQL, bring out the BINARY weapon to instill case sensitivity where there's none.

-- Binary, because why not!
SELECT * FROM table WHERE BINARY column LIKE '%[a-z]%';

Collations: Know your standards

Always check your collation defaults. It's like knowing where North is on your SQL server, database, or column compass!

When to Unleash Regular Expressions

Regex is your mullet: Business in the front (simple case check with UPPER(column) != column), party in the back (complex pattern matching with regex)!

Handling non-ASCII characters: The UTF-8 Lifeline

When your data has non-ASCII characters, opt for a utf-8 collation (utf8_unicode_ci) for accurate case sensitivity.

The Language-Specific Collation advantage

Always ensure that your table encoding speaks the same language (utf8). It's like having the right map for your treasure hunt!

Pattern Matching: Collation got your back

For a successful treasure hunt (case-sensitive search), know the lay of your land (whether your database needs binary comparison or collation tweak).

Use cases: When to engage the regex beast

Regex is like a bazooka; exciting but not for every situation. Use it when you fight battles such as finding patterns like "1234aaaa5789" or "ABCD789xyz".