Using the LOWER() and UPPER() SQL Server Functions

Return rows for a column case insensitive

It is common practice to make a column case insensitive to ensure that you return all of the desired rows. 

SELECT *
FROM  [dbo].[Courses]
WHERE LOWER (education_delivery_method) = 'classroom'

Display a column in uppercase

You can use either the SQL Server UPPER() or LOWER() functions to format columns in your SQL SELECT.

SELECT TOP 10
  UPPER (firstname),
  lastname
FROM Students

Format a column using Upper and Lower functions

It is possible to use the SQL Server LOWER() and UPPER() functions in conjunction with the SUBSTRING() function to accomplish different types of formatting.

SELECT TOP 10
  UPPER(SUBSTRING(lastname,1,1)) + LOWER(SUBSTRING(lastname,2,29)) 
  AS 'Lastname'
FROM Students

Update a group of rows changing case

It is possible to use the SQL Server UPPER() or LOWER() functions in conjunction with an update statement to change the "case" of a group of rows.

BEGIN TRANSACTION
GO

UPDATE [dbo].[Students]
SET    lastname  = UPPER(lastname) 
WHERE  student_id > 0  
GO

COMMIT TRANSACTION
GO
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources