Oracle UPPER function.

Preamble

The Oracle/PLSQL UPPER function converts all characters of a string into upper case. If there are characters in a string that are not letters, they do not affect this function.

Syntax of the Oracle/PLSQL UPPER function

UPPER( string1_id )

Parameters and function arguments

• string1_id – a string to convert to upper case.

The function UPPER returns a string value.

See also the LOWER function.

The UPPER function can be used in the following versions of Oracle/PLSQL

Let’s consider some examples of the UPPER function and learn how to use UPPER function in Oracle/PLSQL.

SQL> SELECT UPPER('Charles Dickens') FROM DUAL;

--Result: CHARLES DICKENS

SQL> SELECT UPPER('Charles Dickens 1812 - 1870 ') FROM DUAL;

--Result: CHARLES DICKENS 1812 - 1870

Q&A

Question:
How do I combine the Oracle UPPER function with LIKE? I try to query text fields of all records containing the word ‘test’. The problem is that it can be entered in one of the following ways: ‘TEST’, ‘Test’ or ‘test’.

The answer is:
To answer this question, let’s look at an example.

Let us assume that we have a table with suppliers in the field called supplier_name that contains values, ‘TEST’, ‘Test’ or ‘test’.

If we wanted to find all entries that contain the word ‘test’, regardless of whether it is stored as ‘TEST’, ‘Test’ or ‘test’, we could run one of the following SQL queries:

select *
from suppls
where UPPER(suppl_name) like ('TEST%');

or 

select *
from suppls
where UPPER(suppl_name) like UPPER('test%')

These SQL queries use a combination of the UPPER function and the LIKE condition, and return all records where the supplier_name field contains the word ‘test’, regardless of whether it was ‘TEST’, ‘Test’ or ‘test’.

The Oracle UPPER function is used to convert a string to an uppercase value

About Enteros

​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​