In Oracle, the NLS_UPPER() function returns its argument with all letters in uppercase.

It’s similar to the UPPER() function, except that it accepts a second argument that allows you to specify the collation. The collation handles special linguistic requirements for case conversions. 

Syntax

The syntax goes like this:

NLS_UPPER(char [, 'nlsparam' ])

Where both char and 'nlsparam' can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 

The 'nlsparam' argument can have the following form:

'NLS_SORT = sort'

Where sort is a named collation.

If you omit this argument, the determined collation of the function is used.

Example

Here’s a simple example to demonstrate:

SELECT NLS_UPPER('coffee time') AS Result
FROM DUAL;

Result:

        RESULT 
______________ 
COFFEE TIME   

The same applies when the argument uses mixed case:

SELECT NLS_UPPER('Coffee Time') AS Result
FROM DUAL;

Result:

        RESULT 
______________ 
COFFEE TIME   

And if the argument is already uppercase, then the result is the same as the input:

SELECT NLS_UPPER('COFFEE TIME') AS Result
FROM DUAL;

Result:

        RESULT 
______________ 
COFFEE TIME   

Specify a Collation

Here’s an example that demonstrates how specifying a collation can alter the results:

SELECT 
    NLS_UPPER('fasilə') AS r1,
    NLS_UPPER('fasilə', 'NLS_SORT = XAZERBAIJANI') AS r2
FROM DUAL;

Result:

       R1        R2 
_________ _________ 
FASILƏ    FASİLƏ   

Notice that the second column uses a dotted uppercase I character, which adheres to the Azerbaijani writing system.

Note that NLS_SORT overrides the collation of the first argument only at the time of execution. Therefore, the following statement returns the collation of the first argument, not the second one:

SELECT
    COLLATION(NLS_UPPER('fasilə', 'NLS_SORT = XAZERBAIJANI')) AS Collation
FROM DUAL;

Result:

        COLLATION 
_________________ 
USING_NLS_COMP   

The subject of collations can be quite complex. See Appendix C in the Oracle Database Globalization Support Guide for the collation determination rules and collation derivation rules for this function.

Null Values

Passing null returns null:

SELECT
    NLS_UPPER(null, 'NLS_SORT = XAZERBAIJANI') AS r1,
    NLS_UPPER('fasilə', null) AS r2,
    NLS_UPPER(null, null) AS r3
FROM DUAL;

Result:

     R1      R2      R3 
_______ _______ _______ 
null    null    null   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling NLS_UPPER() without passing any arguments returns an error:

SELECT NLS_UPPER()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_UPPER()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing too many arguments results in an error:

SELECT NLS_UPPER('coffee', 'NLS_SORT = XAZERBAIJANI', 'time')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_UPPER('coffee', 'NLS_SORT = XAZERBAIJANI', 'time')
FROM DUAL
Error at Command Line : 1 Column : 55
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: