Rajendra Gupta

Overview of SQL LOWER and SQL UPPER functions

September 10, 2019 by

This article gives an overview of SQL UPPER function and SQL LOWER function to convert the character case as uppercase and lowercase respectively.

Introduction

Suppose you have an online shopping website. Customers visit the website and provide their necessary information while creating a login account. Each customer provides few mandatory information such as first name, last name, email address and residential address. Each customer is different so you cannot expect a similar format for all inputs.

For example, you get the following entries in your SQL table. We do not see all words following a consistent pattern. It does not look good as well if you have to share the report daily to higher management for all newly enrolled customers.

In the following table, you can see the different types of inputs from different customers. For example, in [First Name] column, we have Raj (First character capital), sonu (all small case characters) and PAUL (capital letters).

First Name

Last Name

Email address

Residential address

Raj

Gupta

raj.gupta@abc.com

140, high street, la

sonu

Kumar

SONU.KUMAR@XYZ.com

10,KING STREET, GA

PAUL

SMITH

Paul.Smith@Abc.com

20,New Market, SA

You might think of a question at this point – Is it possible to covert the character case in SQL Server to an appropriate format.

Yes, it is quite possible. Let’s explore a few available scenarios in the further section of this article.

SQL UPPER Function

We use SQL UPPER function to convert the characters in the expression into uppercase. It converts all characters into capital letters.

The syntax of SQL Upper function is:

Let’s use some examples for this function and view the output.

Example 1: Use UPPER function with all lower case characters in a single word

It gives the following output.

SQL UPPER function

Example 2: Use UPPER function with all lower case characters in an expression

In this example, we use a string with Upper function, and it converts all letters in the string to uppercase.

Output: It converts all characters for a string.

Use UPPER function with all lower case characters in an expression

Example 3: Use an SQL UPPER function with mix case (combination of the lower and upper case) characters in an expression

In this example, we have the same string from example 2 but with a combination of lower and upper case characters.

It converts all characters regardless of lower and upper case characters.

Use an SQL UPPER function with mix case (combination of lower and upper case) characters in an expression

Example 4: Use the UPPER function with all uppercase characters

In the case of an upper case letter, this function does not perform any operation and return the same string as output.

Output:

Use the UPPER function with all uppercase characters

Example 5: Use an SQL UPPER function with Select statement

We can use SQL UPPER function in a select statement as well and convert the required values to upper case.

In the following query, it creates an employee table and inserts record in it.

Perform a select query on this employee table, and it returns the records in the following format.

sample data

We want values in the country column to be in uppercase. Let’s use the Upper function.

In the output, we can see the uppercase value for a Country column.

Use an UPPER function with Select statement

Example 6: Use an UPPER function with an update statement

We can use this function in an update statement as well. The following query, update an employee table record with uppercase of the [Firstname] column value.

Example 7: Use SQL UPPER function a variable

We can use a variable in T-SQL as well. We can use the upper function with a variable as well.

In the following query, we declare a variable and provide string in it.

Use UPPER function a variable

SQL LOWER function

It works opposite to the SQL UPPER function. It converts uppercase letters to lower case for the specified text or expression.

The syntax of SQL Lower function is

Let’s use some examples and view the output of this function.

Example 1: Use a LOWER function with all lower case characters in a single word

In this example, we use a lower function with all lower case characters. It does not perform any character case conversion for this.

Use a LOWER function with all lower case characters in a single word

Example 2: Use SQL Lower function with all lower case characters in an expression

In this example, we have a string with all lower case characters. We get the same output because of all character already in lower case.

Use SQL Lower function with all lower case characters in an expression

Example 3: Use a LOWER function with mix case (combination of the lower and upper case) characters in an expression

In this example, we have a string that contains both lower and upper case. SQL Lower function ignores the lower characters and converts all uppercase characters into lowercase.

Use a LOWER function with mix case (combination of lower and upper case) characters in an expression

Example 4: Use a Lower function with all uppercase characters

Suppose we have a string with all uppercase letters, and we require converting them into lowercase. SQL Lower function does it for us.

Use a Lower function with all uppercase characters

Example 5: Use a LOWER function with Select statement

In the following example, we use SQL Lower function to convert the [firstname] column values in lowercase.

Use a LOWER function with Select statement

Example 6: Use SQL LOWER function with an update statement

We can use this function in an update statement as well. The following query, update an employee table record with lowercase of the Lastname column.

Example 7: Use the LOWER function in a variable

We can use a lower function with a variable similar to an upper function. Let’s use the same query with lower function.

Use LOWER function in a variable

Convert the first letter of each word in Uppercase

Usually, we do not want all letters in a string converted into all lower or small case letters. Suppose we want the first letter of each word in a text to be in uppercase and rest all characters should be in lower case.

For example, suppose we have the following string, and it has all letters in uppercase.

FRUITS: – APPLE MANGO ORANGE BANANA

We want to covert this text in the following format.

Fruits: – Apple Mango Orange Banana

We do not have SQL function to do this task for us. You need to write a custom function for this. Execute the following script to create a function.

You can provide text in the function as an input value, and you get the required output format.

In the following screenshot, we can see it coverts the upper case of each word’s first letter.

Let’s understand this function and see how it works.

  • We define the text in a variable @text and define a few other variables @Index, @FirstChar, @LastChar and @String
  • @String function converts all text into lowercase using SQL Lower function
  • @String= fruits:- apple mango orange banana
  • It sets the value for the @Index variable as 1
  • LEN function calculates the length of the string using LEN(@text). It returns value 35 for the current string
  • The function starts a while loop. It starts at value 1 and goes up to 35 to perform the following task
    • It gets the first character in the variable @FirstChar using the substring function
    • If the @Index value is 1, then it sets the value for @LastChar as @
    • It moves to next If block and finds the value of @LastChar as @ so it replaces the first character as upper case
    • If the value of the @Index parameter is greater than 1, @FirstChar parameter gets the particular character from the text
    • The @Lastchar gets the character one step behind and does not perform any conversion to uppercase. It increments the value of the @Index parameter and moves next in the while loop

Let’s use a few more examples with this function and observe the output.

Example:

Convert the first letter of each word in Uppercase

Example

Example of convert the first letter of each word in Uppercase

Example

Convert the first letter of each word in Uppercase with special characters

Conclusion

In this article, we explored the SQL UPPER function and SQL LOWER function to convert the characters in uppercase and lowercase respectively. We also create a custom function to convert first letter of each word in the text to capital letter.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
1,094 Views