Overview
The LOWER function is used to make a string of characters or character expressions lower case.
Explanation
Syntax
LOWER(expression)Parameters
- expression – This is the string or expression used that we require to lowercase.
Simple format example
The following example will return lower case characters:
SELECT LOWER('HELLO WORLD') as message
Example working with columns
The following example will lowercase the firstname of the table Person:
SELECT LOWER([FirstName]) as firstname
FROM [AdventureWorks2019].[Person].[Person]
Example comparing strings
By default, SQL Server is case insensitive, but you can change the collation. The following query shows how to check the case sensitiveness:
SELECT SERVERPROPERTY('COLLATION') as collationThe result may be something like this: SQL_Latin1_General_CP1_CI_AS. If it says CI, the SQL Server is case insensitive.
If your result is something like this: SQL_Latin1_General_CP1_CS_AS Your SQL Server is case sensitive.
In that case, the LOWER function can be useful to find strings that may or not may be lowercased. The following example show how to compare strings by doing a lowercase to the column and the string characters in order to force the same case sensitivity.
SELECT BusinessEntityID,FirstName, LastName
FROM [AdventureWorks2019].[Person].[Person]
WHERE LOWER(FirstName)=LOWER('kim')
Proper Case Using UPPER and LOWER Example
Here is an example where we can use UPPER and LOWER to get the proper case for a name. In the example below we create a temp table and load some sample data. We are also using the LEFT function and SUBSTRING function.
CREATE TABLE #temp (FirstName nvarchar(20))
INSERT INTO #temp
VALUES ('kim'), ('sam'), ('adam')
SELECT FirstName, UPPER(LEFT(FirstName,1)) + LOWER(SUBSTRING(FirstName,2,100)) as ProperCase
FROM #tempHere is the output.
We could also write the query this way using RIGHT instead of SUBSTRING.
SELECT FirstName, UPPER(LEFT(FirstName,1)) + LOWER(RIGHT(FirstName,2)) as ProperCase
FROM #tempExample with LOWER and UPPER
UPPER is used to change lowercased string characters to uppercase. The following example shows that if we apply the lower function to an upper function, the result is lowercased:
SELECT LOWER(UPPER('Hello World')) as message
Additional Information
