PostgreSQL UPPER() String Function

PostgreSQL UPPER() String Function

The PostgreSQL UPPER() function is used to convert all characters in a string to uppercase. This function is essential for standardizing text data and performing case-insensitive comparisons.

---

Syntax

UPPER(string)

The UPPER() function has the following component:

• string: The string to be converted to uppercase.

---

Example PostgreSQL UPPER() Queries

Let's look at some examples of PostgreSQL UPPER() function queries:

1. Basic UPPER() Example

SELECT UPPER('Hello, World!') AS uppercase_string;

This query converts the string 'Hello, World!' to uppercase, resulting in 'HELLO, WORLD!'.

2. UPPER() with Mixed Case

SELECT UPPER('PostgreSQL Upper Function') AS uppercase_string;

This query converts the string 'PostgreSQL Upper Function' to uppercase, resulting in 'POSTGRESQL UPPER FUNCTION'.

3. UPPER() with Column Values

SELECT id, name, UPPER(name) AS uppercase_name
FROM users;

This query retrieves the id, name, and the uppercase version of the name for each row in the users table.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the UPPER() function to standardize text data.

Step 1: Creating a Table

This step involves creating a new table named users to store user data.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

In this example, we create a table named users with columns for id and name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the users table.

INSERT INTO users (name)
VALUES ('Alice'),
       ('Bob'),
       ('Charlie');

Here, we insert data into the users table.

Step 3: Using the UPPER() Function

This step involves using the UPPER() function to standardize the text data in the users table.

Basic UPPER()

SELECT UPPER('Hello, World!') AS uppercase_string;

This query converts the string 'Hello, World!' to uppercase.

UPPER() with Mixed Case

SELECT UPPER('PostgreSQL Upper Function') AS uppercase_string;

This query converts the string 'PostgreSQL Upper Function' to uppercase.

UPPER() with Column Values

SELECT id, name, UPPER(name) AS uppercase_name
FROM users;

This query converts the values in the 'name' column of the 'users' table to uppercase.

These queries demonstrate how to use the UPPER() function to standardize the text data in the users table, including basic usage and handling mixed case.

Conclusion

The PostgreSQL UPPER() function is a fundamental tool for standardizing text data by converting all characters in a string to uppercase. Understanding how to use the UPPER() function and its syntax is essential for effective text data manipulation in PostgreSQL databases.