This article gives an overview of SQL UPPER function and SQL LOWER function to convert the character case as uppercase and lowercase respectively.
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).
140, high street, la
10,KING STREET, GA
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:
SELECT UPPER(expression) FROM [Source Data]
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.
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.
SELECT UPPER('learn sql server with sqlshack');
Output: It converts all characters for a string.
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.
SELECT UPPER('Learn SQL server with sqlshack');
It converts all characters regardless of lower and upper case characters.
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.
SELECT UPPER('LEARN SQL SERVER WITH SQLSHACK');
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.
Create table Employee
Insert into Employee values ('Rajendra','Gupta','India')
Perform a select query on this employee table, and it returns the records in the following format.
We want values in the country column to be in uppercase. Let’s use the Upper function.
upper(Country) as COUNTRY
In the output, we can see the uppercase value for a Country column.
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.
Update [dbo].[Employee] set [Firstname] =upper('raj') where ID=1
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.
DECLARE @text VARCHAR(30);
SET @text = 'This is a sample text';
SELECT @text as Input,
UPPER(@text) AS UpperCase;
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
SELECT Lower (Expression) FROM Source
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.
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.
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.
SELECT Lower('Learn SQL server with sqlshack');
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.
SELECT Lower('LEARN SQL SERVER WITH SQLSHACK');
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.
lower(Country) as COUNTRY
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.
Update [dbo].[Employee] set [lastname]=Lower('RAJ')
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.
DECLARE @text VARCHAR(30);
SET @text = 'This is a sample text';
LOWER(@text) AS Lowercase;
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.
CREATE OR ALTER FUNCTION [dbo].[ConvertFirstLetterinCapital](@Text VARCHAR(5000)) RETURNS VARCHAR(5000) AS BEGIN
DECLARE @Index INT;
DECLARE @FirstChar CHAR(1);
DECLARE @LastChar CHAR(1);
DECLARE @String VARCHAR(5000);
SET @String = LOWER(@Text);
SET @Index = 1;
WHILE @Index <= LEN(@Text)
SET @FirstChar = SUBSTRING(@Text, @Index, 1);
SET @LastChar = CASE
WHEN @Index = 1
THEN ' '
ELSE SUBSTRING(@Text, @Index - 1, 1)
IF @LastChar IN(' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(', '#', '*', '$', '@')
IF @FirstChar != ''''
OR UPPER(@FirstChar) != 'S'
SET @String = STUFF(@String, @Index, 1, UPPER(@FirstChar));
SET @Index = @Index + 1;
You can provide text in the function as an input value, and you get the required output format.
Select [dbo].[ConvertFirstLetterinCapital] ('FRUITS :- APPLE MANGO ORANGE BANANA')
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.
Select [dbo].[ConvertFirstLetterinCapital] ('fruits :- apple mango orange banana')
Select [dbo].[ConvertFirstLetterinCapital] ('fruits :- @apple mango$ orange banana')
Select [dbo].[ConvertFirstLetterinCapital] ('FRUITS :- $%&*ABC mango2 orange banana')
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.
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 firstname.lastname@example.org
View all posts by Rajendra Gupta