PostgreSQL LOWER() function

LOWER() function

The PostgreSQL lower function is a built-in function used to convert all uppercase characters in a given string to lowercase. This function is particularly useful for case-insensitive comparisons, data normalization, and ensuring consistency in string data across your database.

Uses of LOWER() Function

• Data Normalization: Ensures consistency in string data by converting all characters to lowercase.


• Case-Insensitive Comparisons: Facilitates comparisons and searches that ignore case differences.


• Data Cleaning: Helps in cleaning and standardizing data by converting mixed-case strings to lowercase.


• User Input Processing: Standardizes user input to lowercase for consistent storage and processing.


• Text Analysis: Prepares text data for analysis by normalizing case differences.


• Display Formatting: Converts text to a uniform case for display purposes.

Syntax:

lower(string)

Parameters:

PostgreSQL Version

• Compatible with PostgreSQL version 9.3 and later.

Visual Presentation

Example: PostgreSQL LOWER() function

The following PostgreSQL statement returns the given string after converting all of its characters in lowercase.

Code:

SELECT lower('W3RESOURCE') AS "Upper to Lower";

Sample Output:

 Upper to Lower
----------------
 w3resource
(1 row)

Example: LOWER() on column of a table

Sample Table: employees.

If we want to display the first name, last name, email and the lower of email of those employees who belong to the dept 20 from the employee table, the following SQL can be executed:

Code:

SELECT first_name,last_name,
email,LOWER(email)
FROM employees
WHERE department_id=20;

Sample Output:

 first_name | last_name |  email   |  lower
------------+-----------+----------+----------
 Michael    | Hartstein | MHARTSTE | mhartste
 Pat        | Fay       | PFAY     | pfay
(2 rows)

Example LOWER(): Using with TEXT Type

Code:

SELECT lower(CAST('HELLO' AS TEXT)) AS "Lowercase Text";

Sample Output:

Lowercase Text|
--------------+
hello         |

Example LOWER(): Using with CHAR Type

Code:

SELECT lower(CAST('HELLO' AS CHAR(10))) AS "Lowercase Char";

Sample Output:

Lowercase Char|
--------------+
hello         |

Example LOWER(): Using with VARCHAR Type

Code:

SELECT lower(CAST('HELLO' AS VARCHAR(10))) AS "Lowercase Varchar";

Sample Output:

Lowercase Varchar|
-----------------+
hello            |

Example LOWER(): Using with CONCAT() Function

Code:

SELECT lower(CONCAT('Hello', 'World')) AS "Lowercase String";

Sample Output:

Lowercase String|
----------------+
helloworld      |

Example LOWER(): Using with SUBSTRING() Function

Code:

SELECT lower(SUBSTRING('HELLO WORLD' FROM 1 FOR 5)) AS "Lowercase Substring";

Sample Output:

Lowercase Substring|
-------------------+
hello              |

Example LOWER(): Using with TRIM() Function

Code:

SELECT lower(TRIM('  HELLO  ')) AS "Lowercase Trimmed";

Sample Output:

Lowercase Trimmed|
-----------------+
hello            |

Previous: CHAR_LENGTH function
Next: OCTET_LENGTH function