{"id":919,"date":"2016-07-15T05:09:38","date_gmt":"2016-07-15T05:09:38","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=919"},"modified":"2025-02-04T06:17:27","modified_gmt":"2025-02-04T13:17:27","slug":"sql-upper","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-string-functions\/sql-upper\/","title":{"rendered":"SQL UPPER Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL UPPER<\/code> function to return a new string with all characters converted to uppercase.<\/p>\n\n\n\n

Introduction to the SQL UPPER function #<\/a><\/h2>\n\n\n\n

The UPPER <\/code>function accepts a string and returns a new string with all characters converted to uppercase.<\/p>\n\n\n\n

Here’s the syntax of the UPPER<\/code> function:<\/p>\n\n\n

UPPER(string)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The UPPER<\/code> function returns a new string with all characters of the string<\/code> converted to uppercase. If the string<\/code> is NULL<\/code>, the UPPER<\/code> function returns NULL<\/code>.<\/p>\n\n\n\n
Basic SQL UPPER function example #<\/a><\/h2>\n\n\n\n
The following statement uses the UPPER<\/code> function to convert the string sql upper<\/code> to uppercase:<\/p>\n\n\n
SELECT<\/span>\n  UPPER<\/span>('sql upper'<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
  result\n-----------<\/span>\n SQL UPPER<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query uses the UPPER<\/code> function with NULL<\/code>:<\/p>\n\n\n
SELECT\n  UPPER(NULL<\/span>) result;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------\n NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Transforming data to uppercase #<\/a><\/h2>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following query<\/a> uses the UPPER<\/span> function to retrieve data from the last_name<\/code> column and convert it to uppercase:<\/p>\n\n\n
SELECT<\/span>\n  UPPER<\/span>(last_name) formatted_last_name\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  formatted_last_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 formatted_last_name\n---------------------\n AUSTIN\n BAER\n BAIDA\n BELL\n CHEN\n...<\/code><\/span><\/pre>\n\n\n
In this example, we retrieve data from the last_name<\/code> column of the employees<\/code> table and convert them to uppercase on the fly. The data in the last_name<\/code> column of the employees<\/code> remains intact.<\/p>\n\n\n\n
Filtering data case-insensitively #<\/a><\/h2>\n\n\n\n
When filtering data using the WHERE<\/code><\/a> clause, database systems match character strings case-sensitively. For example, the literal string Bruce<\/code> is different from bruce<\/code>.<\/p>\n\n\n\n
To match text string case-insensitively, you use the UPPER<\/code> function in the WHERE<\/code> clause like this:<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name\nFROM<\/span>\n  employees\nWHERE<\/span>\n  UPPER<\/span>(first_name) = 'BRUCE'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that this query scans the whole table to find the matching row. In case the table is big, it will be slow.<\/p>\n\n\n\n
To address this, some database systems provide a function-based index that allows you to define an index based on a function of one or more columns, which results in better query performance.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n