SQLite | SQLite Functions | Converting Strings to Uppercase or Lowercase (lower and upper Functions)
The lower function converts every character in a string to lowercase, while the upper function converts every character to uppercase. This article explains how to use both functions.
Using the lower and upper Functions
The lower function converts a string to lowercase. Its syntax is as follows.
lower(string)
It converts every character in the supplied string to lowercase. When you specify a column, it converts the values stored in that column.
The upper function converts a string to uppercase. Its syntax is as follows.
upper(string)
It converts every character in the supplied string to uppercase. When you specify a column, it converts the values stored in that column.
–
To try these functions, first create a table.
create table fruit (id integer, name text);
sqlite> create table fruit (id integer, name text);
sqlite>
Insert the following data with INSERT statements.
insert into fruit values (1, 'Apple');
insert into fruit values (2, 'KiwiFruit');
insert into fruit values (3, 'Peach');
insert into fruit values (4, 'Strawberry');
sqlite> insert into fruit values (1, 'Apple');
sqlite> insert into fruit values (2, 'KiwiFruit');
sqlite> insert into fruit values (3, 'Peach');
sqlite> insert into fruit values (4, 'Strawberry');
sqlite>
Use lower and upper to retrieve the values in the name column in lowercase and uppercase.
select name, lower(name), upper(name) from fruit;
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select name, lower(name), upper(name) from fruit;
name lower(name) upper(name)
---------- ----------- -----------
Apple apple APPLE
KiwiFruit kiwifruit KIWIFRUIT
Peach peach PEACH
Strawberry strawberry STRAWBERRY
sqlite>
The stored values are returned in both lowercase and uppercase.
You can also pass a string directly to each function.
select lower('Flower'), upper('Flower');
sqlite> select lower('Flower'), upper('Flower');
lower('Flower') upper('Flower')
--------------- ---------------
flower FLOWER
sqlite>
The lower function returns the lowercase form, and the upper function returns the uppercase form.