How To Use SQL LOWER Function To Convert a String Into Lowercase

Summary: in this tutorial, you will learn how to use the SQL LOWER function to convert all characters of a string into lowercase.

Introduction to the SQL LOWER function

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.

LOWER(string);Code language: SQL (Structured Query Language) (sql)

The LOWER function returns a string with all characters in the lowercase format. It returns NULL if the input string is NULL.

Some database systems such as Oracle database and MySQL provide the LCASE function that is equivalent to the LOWER function.

LCASE(string);Code language: SQL (Structured Query Language) (sql)

SQL LOWER examples

The following statement uses the LOWER function to convert a string to lowercase:

SELECT LOWER('SQL LOWER');Code language: SQL (Structured Query Language) (sql)

        lower
----------------------
sql lower
(1 row)Code language: SQL (Structured Query Language) (sql)

See the following employees table in the sample database.

The following query uses the LOWER function to return the names of departments in lowercase.

SELECT 
    LOWER(department_name)
FROM
    departments
ORDER BY LOWER(department_name);Code language: SQL (Structured Query Language) (sql)

The following statement updates the emails of the employees to lowercase.

UPDATE employees 
SET 
    email = LOWER(email);Code language: SQL (Structured Query Language) (sql)

Querying data case insensitive

Standard SQL performs matching case sensitive. It means that the literal string Sarah is different from sarah when it comes to an input for a query.

To query data case-insensitive, you can use the LOWER function.

The following query returns an empty result set because there is no employee whose first name is sarah.

SELECT 
    employee_id, first_name, last_name, email
FROM
    employees
WHERE
    first_name = 'sarah';Code language: SQL (Structured Query Language) (sql)

However, when you use the LOWER function, it returns a row.

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    email
FROM
    employees
WHERE
    LOWER(first_name) = 'sarah';Code language: SQL (Structured Query Language) (sql)

Note that this query scans the whole table to get the result. For the big table, it will be slow.

Some database systems support the function-based index e.g., Oracle database, PostgreSQL, etc., You can create an index based on a specific function. If you create a function-based index for the first_name column, the query will use the index to find the row very fast.

Now you should know how to use the LOWER function to convert a string into lowercase.

Was this tutorial helpful ?