How to Change Text to Lowercase in SQL

Problem:

You’d like to change some text to lowercase in SQL.

Example:

Our database has a table named item with data in the following columns: id, code, and wood_type_used. The case is inconsistent in the data in the wood_type_used column. We would like all of the wood types to be displayed in lowercase.

Solution:

We’ll use the LOWER() function:

SELECT
  code,
  LOWER(wood_type_used)
FROM item;

The query returns the items with wood types written in lowercase letters:

Discussion:

Whenever you want some text data from your SQL database to be displayed in lowercase, use the LOWER() function. This function takes as an argument a string or the name of a column whose text values are to be displayed in lowercase. It returns a version of the original text data in which every uppercase letter is replaced by its lowercase equivalent, and the rest of the characters remain unchanged.

However, displaying text in a uniform way is not the only application of the LOWER() function. You may also find it helpful in case-insensitive queries, that is, queries in which it is irrelevant how the text data you are looking for is originally cased.

For instance, in our example table above, you might want to find all items made of birch wood. Since values of the wood type are not consistently cased, simply querying for all rows that have 'birch' in the wood_type_used column would only return one row, even though two of the listed products are clearly made of birch. This is where LOWER() comes in:

SELECT *
FROM item
WHERE LOWER(wood_type_used) = 'birch';

Such a query will return all rows with some form of 'birch' string in the wood_type_used column, regardless of how the values are cased in the original data. Remember to write the text you are searching for in lowercase as well.