PostgreSQL UPPER() function
UPPER() function
The PostgreSQL upper function is used to convert all characters in a given string to uppercase. This function is particularly useful for standardizing text data, ensuring consistent formatting for string comparisons, and preparing data for display purposes.
Uses of UPPER() Function
- Standardizing Text Data: Convert text to uppercase to ensure consistency across datasets.
- Improving Data Comparisons: Facilitate accurate string comparisons by converting both strings to uppercase.
- Data Cleaning: Uniformly format text data for easier manipulation and analysis.
- Preparing Data for Display: Ensure text is in uppercase for specific display requirements or conventions.
- Creating Uniform Identifiers: Standardize identifiers by converting them to uppercase.
- Formatting User Input: Convert user input to uppercase to match specific data format requirements.
- Query Optimization: Simplify queries by converting text fields to uppercase for consistent search results.
Syntax:
upper(string)
PostgreSQL Version: 9.3
Visual Presentation of PostgreSQL UPPER() function
Example: PostgreSQL UPPER() function:
In the following example PostgreSQL upper function returns lower case to uppercase.
SQL Code:
SELECT upper('w3resource');
Output:
upper ------------ W3RESOURCE (1 row)
PostgreSQL UPPER() function using Column :
Sample Table: employees.
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100
..................
206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110
If we want to display the employee_id, first name, and first_name in upper case for those employees who belong to the department which department_id is 100 from employees table , the following SQL can be executed:
SQL Code:
SELECT employee_id,first_name,
upper(first_name) "Upper case of first_name"
FROM employees
WHERE department_id=100;
Output:
employee_id | first_name | Upper case of first_name
-------------+-------------+--------------------------
108 | Nancy | NANCY
109 | Daniel | DANIEL
110 | John | JOHN
111 | Ismael | ISMAEL
112 | Jose Manuel | JOSE MANUEL
113 | Luis | LUIS
(6 rows)
PREV : TRIM function
NEXT : ASCII function