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:
|
1 |
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
|
1 |
SELECT UPPER('sqlshack'); |
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.
|
1 |
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.
|
1 |
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.
|
1 |
SELECT UPPER('LEARN SQL SERVER WITH SQLSHACK'); |
Output:
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.
|
1 2 3 4 5 6 7 8 |
Create table Employee ( Firstname varchar(20), Lastname varchar(20), Country varchar(20) ) Insert into Employee values ('Rajendra','Gupta','India') |
Perform a select query on this employee table, and it returns the records in the following format.
|
1 2 3 4 |
SELECT Firstname, Lastname, Country FROM Employee; |
We want values in the country column to be in uppercase. Let’s use the Upper function.
|
1 2 3 4 |
SELECT Firstname, Lastname, upper(Country) as COUNTRY FROM Employee; |
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.
|
1 |
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.
|
1 2 3 4 |
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
|
1 |
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.
|
1 |
SELECT Lower('sqlshack'); |
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.
|
1 |
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.
|
1 |
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.
|
1 2 3 4 |
SELECT Firstname, Lastname, lower(Country) as COUNTRY FROM Employee; |
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.
|
1 |
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.
|
1 2 3 4 |
DECLARE @text VARCHAR(30); SET @text = 'This is a sample text'; SELECT @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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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) BEGIN SET @FirstChar = SUBSTRING(@Text, @Index, 1); SET @LastChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@Text, @Index - 1, 1) END; IF @LastChar IN(' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(', '#', '*', '$', '@') BEGIN IF @FirstChar != '''' OR UPPER(@FirstChar) != 'S' SET @String = STUFF(@String, @Index, 1, UPPER(@FirstChar)); END; SET @Index = @Index + 1; END; RETURN @String; END; GO |
You can provide text in the function as an input value, and you get the required output format.
|
1 |
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.
Example:
|
1 |
Select [dbo].[ConvertFirstLetterinCapital] ('fruits :- apple mango orange banana') |
Example
|
1 |
Select [dbo].[ConvertFirstLetterinCapital] ('fruits :- @apple mango$ orange banana') |
Example
|
1 |
Select [dbo].[ConvertFirstLetterinCapital] ('FRUITS :- $%&*ABC mango2 orange banana') |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023