How to Use the SQL LOWER Function for Case Insensitive Queries

The SQL LOWER function converts the input character data to a lower case.

Description

The LOWER function takes character expression as an input and converts it into a lower case text.

It helps to perform a case-insensitive search in the database. The SQL search is case-sensitive. The value Lower and lower are different. The Lower function converts all the column values to a lower case, and then a search is performed.

The SQL LOWER function also helps in the consistent display of database values for a better view. For example, in a database column that stores the users’ input values, users can enter values differently. The LOWER function converts all such database values to a lower case before the display.

SQL LOWER Function Usage Notes

The input expression is of character or binary data type. It can be a variable, constant, or column value. The data type of the input expression must be implicitly convertible to varchar. Otherwise, the CAST function can explicitly convert such values to varchar.

The output is NULL in case the input expression is NULL. The expression remains unchanged in the case of upper case characters.

The output data type is varchar or nvarchar.

Syntax                                      

LOWER (input_character_expression)

SQL LOWER Function Examples

The following examples show the use of the LOWER function in SQL.

/* Answer */
SELECT LOWER('CONVERT TO LOWER CASE') Example;

Here is an example using mixed case:

/* Answer */
SELECT LOWER('Convert to Lower Case') MixedCaseExample;

The following example uses the AdventureWorks2019 database.

/* Answer */
SELECT LOWER(Name) LowerName
FROM HumanResources.Department
ORDER BY Name

The query returns the department names in the lower case ordered alphabetically.

See Also

• Introduction to SQL Server’s String Functions
• Upper Function
• String Functions
• LEFT Function
• RIGHT Function
• SUBSTRING Function