sql_func_ucase

SQL UPPER() / UCASE() Function

The Uppercase Conversion Function

SQL provides functions to convert string values to all uppercase letters. This is useful for case-insensitive comparisons, data standardization, or display formatting.

The standard SQL function for this is UPPER(). Some database systems also provide UCASE() as an alias or alternative.

SQL UPPER() / UCASE() Syntax

Standard SQL & Most Databases (MySQL, PostgreSQL, Oracle, SQL Server): UPPER(string)

SELECT UPPER(column_name) FROM table_name;

MySQL also supports UCASE(string) as a synonym for UPPER():

SELECT UCASE(column_name) FROM table_name; -- Primarily MySQL

It’s generally recommended to use UPPER() for better portability across different SQL database systems.

Demo Table Context

Let’s assume we have a Customers table with columns like CustomerName and City, which store text data that might have mixed casing.

SQL UPPER() Example

The following SQL statement selects the CustomerName converted to uppercase (aliased as UppercaseCustomerName) and the original City from the Customers table:

SELECT UPPER(CustomerName) AS UppercaseCustomerName, City
FROM Customers;

If CustomerName was ‘Alfreds Futterkiste’, the UppercaseCustomerName column would show ‘ALFREDS FUTTERKISTE’.

Practical Applications:

Case-Insensitive Search: To find a customer named ‘smith’ regardless of how it’s stored (e.g., ‘Smith’, ‘SMITH’, ‘smith’), you can use:

SELECT CustomerName, City
FROM Customers
WHERE UPPER(LastName) = 'SMITH';

(Note: Many databases offer case-insensitive collations which might be more efficient for searches than applying UPPER() to the column in the WHERE clause, as the latter can prevent index usage. However, UPPER() on the search term itself is common.)

Data Standardization: Before inserting or updating data, convert certain text fields (like state codes or categories) to uppercase to maintain consistency.

Display Formatting: Ensure consistency in how names or titles are displayed in reports or user interfaces.

Correspondingly, there is also a LOWER() (or LCASE()) function to convert strings to lowercase.