upper() function in SQL
The upper() function is used to convert all the letters in a specified string to uppercase. This function is essential for formatting outputs, ensuring consistency in case-sensitive operations, and facilitating case-insensitive searches by standardizing text data to a uniform case.
Syntax
The basic syntax for the upper() function is:
SELECT UPPER(column_name)
FROM table_name
It accepts a single text argument: the column or string expression you wish to convert to uppercase.
Here’s a basic example that doesn’t even require a table:
SELECT upper('foobar')
Using upper() in practice
Imagine a database containing product information where the product names are stored in varying cases. To standardize the output for reporting or searching purposes, you can convert all product names to uppercase:
SELECT upper(name) AS product_name
FROM products
This query not only converts the names but also aliases the result column as product_name for better readability in the results.
Combining upper() with other functions
upper() can be effectively combined with other SQL functions for more complex queries.
For example, if you are looking for all monthly product entries, regardless of case, you could use:
SELECT *
FROM products
WHERE
upper(name) LIKE '%MONTHLY%'
This approach ensures that the search is case-insensitive by converting the column values to uppercase.
Database compatibility
|
MySQL
|
PostgreSQL
|
SQLite
|
Redshift
|
Big Query
|
Snowflake
|
|
|---|---|---|---|---|---|---|
upper |
|
|
|
|
|
|
upper
