Db2 LOWER

Summary: in this tutorial, you will learn how to use the Db2 LOWER() function to transform all characters of a string to lowercase.

DB2 LOWER() function overview

The LOWER() function accepts a string and returns a new string in which all characters in the original string are converted to lowercase.

The following shows the syntax of the LOWER() function:

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

In this syntax, the expression is a character string expression that evaluates to a string or a value which can be implicitly converted to a string.

Similar to the UPPER() function, the LOWER() function can be useful for case insensitive searches.

Db2 LOWER() function examples

Let’s take some examples of using the LOWER() function.

1) Using Db2 LOWER() to convert a literal string to lowercase

The following example uses the LOWER() function to convert the string 'Db2 lower' to uppercase:

SELECT
    LOWER('Db2 lower') result
FROM
    sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

RESULT    
--------- 
db2 lower 
Code language: SQL (Structured Query Language) (sql)

2) Using Db2 LOWER() to perform case insensitive searches

See the following publishers table in the sample database:

This example uses the LOWER() function to find the publisher whose name is apress:

SELECT
    publisher_id,
    name 
FROM publishers
WHERE 
    LOWER(name) = 'apress';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

PUBLISHER_ID NAME                                                                    
------------ ------- 
14           Apress 
Code language: SQL (Structured Query Language) (sql)

To improve the speed of the query, you can create an expression-based index on the name column of the publishers table:

CREATE INDEX ix_uname
ON publishers(UPPER(name));
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 LOWER() function to convert all characters of a string to lowercase.

Was this tutorial helpful ?