LOWER Function in SQL Server

In this SQL Server tutorial, you learn about the LOWER function in SQL Server. Where you will be introduced to the syntax of the LOWER() function.

Then you will learn how to use the LOWER() function with uppercase, mixed-case string, and string containing the special characters.

In the end, you will learn how to use the LOWER() function with the table column.

Table of Contents

The LOWER() function in SQL Server changes the case of the characters in the string to lowercase. In other words, if you have a string such as ‘SQL’, then LOWER() function converts this string to lowercase such as in ‘sql’.

Moreover, it changes the capital letters to small letters, if the string contains any other characters other than alphabets such as #, &, *, etc. Then the LOWER() function doesn’t convert those characters, and that kind of character remains unchanged.

The syntax of the LOWER() function is given below.

LOWER(str_exp)

Where,

LOWER(): It is the function to change the string to lowercase.
str_exp: It is the string expression, and can be characters, strings, constants, variables, or table columns. The data type of the string expression should be implicitly convertible to varchar datatype.

The return type of the LOWER() function is varchar or nvarchar datatype. This means after converting the string expression to lowercase, it returns a string of nvarchar or varchar datatype.

Let’s see at different examples to know how to use the LOWER() function.

LOWER Function in SQL Server: Uppercase Literal String

If you have a string value in uppercase, then you can change that string to lowercase. For example, you have the string ‘I AM POLICE OFFICER IN CHICAGO’.

To change that string into lowercase use the below query.

SELECT LOWER('I AM POLICE OFFICER IN CHICAGO') AS ToLowerCase;

LOWER Function in SQL Server Uppercase Literal String

When the above query is executed, it converts the string in uppercase to lowercase. For example, the string ‘I AM POLICE OFFICER IN CHICAGO’ is converted to ‘i am police officer in chicago’ which you can see in the above output.

This is how to convert the uppercase string to lowercase using the LOWER function in SQL Server.

Next, let’s see how to change the mixed-case string to lowercase.

LOWER Function in SQL Server: Mixed-Case

The string containing both the cases, the uppercase and lowercase characters, the LOWER() function can also convert that string into only lowercase.

Suppose you have a string of mixed-case such as ‘Chicago is the largest city in the U.S.’, as you can see this string contains both uppercase and lowercase letters.

Now use the below query to convert that string to lowercase.

SELECT LOWER('Chicago is the largest city in the U.S.') AS ToLowerCase;

As you can see in the above output of the query, the provided string ‘Chicago is the largest city in the U.S.’ to LOWER() function is converted to a lowercase string ‘chicago is the largest city in the u.s.’.

LOWER Function in SQL Server: with Other Characters

The LOWER() function doesn’t change the characters other than alphabets in the string. For example, you have the email address ‘TYLOR@GMAIL.COM’, here this mail contains the @ characters and the dot (.).

Let’s see convert that email address to lower using the below query.

SELECT LOWER('TYLOR@GMAIL.COM') AS ToLowerCase;

LOWER Function in SQL Server with Other Characters

Look at the above output of the query in the picture, the characters such as @ and dot (.) are not affected by the LOWER() function, but the function converted all the capital letters to the small letter such as ‘tylor@gmail.com’ that you can see in the above output.

Thus, when the LOWER() function converts the string to lowercase, then special characters with that string remain unchanged or not affected by the LOWER() function.

This is how to use the LOWER() function with a string containing the special characters.

Now let’s learn how to use the LOWER() function with table columns.

LOWER Function in SQL Server: with Table Column

If you table in the database and you need to convert the column value to lowercase for different purposes such as for comparison, search, or sorting. Then you can use the LOWER() function for that purpose.

For example, you have access to the Employees table with columns FirstName, LastName which is shown below.

LOWER Function in SQL Server with Employees Table Column

Now suppose, you have to retrieve the first name of the employees in lowercase. For that use the below query.

SELECT LOWER(FirstName) AS LowercaseFirstName FROM Employees;

LOWER Function in SQL Server with Table Column

When you execute the above query, the LOWER() function converts the first name of the employees to lowercase as you can see in the above output. For example, the first name of the employees Ronan, Kaden, and Walter is converted to ronan, kaden, and water respectively.

Here you are using the LOWER() function with the FirstName column of the Employees table to convert the value of that column into lowercase. This is how to use the LOWER function in SQL Server with a table column.

Conclusion

In this SQL Server tutorial, you covered how to use the LOWER() function in SQL Server to convert the string to lowercase in SQL Server. Also learned about using the LOWER() function with uppercase, mixed-cast string, and with a string containing special characters. Finally learned how to use the LOWER() function with the table column.