SQL Server LOWER Function


By:

The LOWER function is used to make a string of characters or character expressions lower case.

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
query results

Example working with columns

The following example will lowercase the firstname of the table Person:

SELECT LOWER([FirstName]) as firstname
FROM [AdventureWorks2019].[Person].[Person]
query results

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 collation

The 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')
query results

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 #temp

Here is the output.

query results

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 #temp
query results

Example 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
query results

Related Articles


Last Update: 11/8/2021




Comments For This Article





download














get free sql tips
agree to terms