SQL LOWER Function

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

Introduction to the SQL LOWER function #

The SQL LOWER function converts all the characters in a string into lowercase.

Here’s the syntax of the LOWER function:

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

In this syntax:

string: The string you want to convert to all characters to lowercase.

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

If you want to convert all characters in a string into uppercase, you should use the UPPER function.

Basic SQL LOWER function example #

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

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

Try it

Output:

    result
--------------
 sql tutorialCode language: SQL (Structured Query Language) (sql)

Using the SQL LOWER function with table data #

We’ll use the following employees table in the sample database.

The following query uses the LOWER function to convert the employees’ first names to lowercase:

SELECT
  first_name,
  lower(first_name) formatted_first_name
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | formatted_first_name
-------------+----------------------
 Adam        | adam
 Alexander   | alexander
 Alexander   | alexander
 Britney     | britney
 Bruce       | bruce
...

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

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

Filtering strings case-insensitively #

The equal to operator compares string case-sensitively. For example, the literal string Sarah is different from sarah .

To query data case-insensitively, you can use the LOWER function in the WHERE clause as follows:

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

Try it

Output:

 employee_id | first_name | last_name |           email
-------------+------------+-----------+----------------------------
         192 | Sarah      | Bell      | [email protected]

This query may scan the whole employees table to find the row. If the table has many rows, the query will be slow.

Some database systems support the function-based index e.g., Oracle database and PostgreSQL. To speed up the query, you can create an index based on a specific function. For example, if you create a function-based index for the first_name column, the query will use the index to find the row quickly.

Summary #

Use the LOWER function to convert a string into lowercase.

Databases #

Was this tutorial helpful ?