sql_func_lcase

SQL LOWER() Function (formerly LCASE())

The LOWER() Function

The standard SQL function LOWER() converts all characters in a string to lowercase. Some database systems, like MySQL, also support LCASE() as a synonym for LOWER(). For maximum portability, it’s recommended to use LOWER().

This function is useful for data normalization, case-insensitive comparisons, or consistent data display.

SQL LOWER() Syntax

SELECT LOWER(column_name)
FROM table_name;

Or, when applied to a literal string:

SELECT LOWER('Your String HERE'); -- Results in 'your string here'

Demo Database Schema

For our example, let’s assume a Products table with the following relevant columns:

Products Table:
- ProductID (INT, Primary Key)
- ProductName (VARCHAR)
- Category (VARCHAR)

Imagine the ProductName column might contain values with mixed casing like ‘Laptop Pro’, ‘laptop pro’, or ‘LAPTOP PRO’.

SQL LOWER() Example

The following SQL statement selects the ProductName and Category columns from the Products table. It converts the ProductName values to lowercase and aliases the resulting column as LowercaseProductName.

SELECT
    ProductName,
    LOWER(ProductName) AS LowercaseProductName,
    Category
FROM Products;

If Products table had a row with ProductName = ‘Super Widget V2’ and Category = ‘Gadgets’, the result for that row would be:

ProductName     | LowercaseProductName | Category
----------------|----------------------|-----------
Super Widget V2 | super widget v2      | Gadgets

Practical Application:

• Case-Insensitive Searches: When searching for data, you can convert both the column value and the search term to lowercase to ensure matches regardless of casing. For example: WHERE LOWER(Email) = LOWER('User@Example.COM');
• Data Cleaning/Standardization: Before storing user input or data from various sources, converting certain text fields (like tags or categories) to lowercase can help maintain consistency.
• Display Uniformity: Presenting data in a uniform case (e.g., all lowercase usernames) on a user interface.

Note on Performance: Applying functions like LOWER() to a column in a WHERE clause can sometimes prevent the database from using an index on that column efficiently. For frequent case-insensitive searches on large tables, consider database-specific features like case-insensitive collations or functional indexes if available.

Related Function: UPPER() (or UCASE()) converts strings to uppercase.