In Oracle, the NLS_INITCAP() function returns its argument with the first letter of each word in uppercase, and all other letters in lowercase.

However, the actual capitalisation will depend on the collation being used. For example, it could result in more than the first letter being capitalised.

It’s similar to the INITCAP() 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_INITCAP(char [, 'nlsparam' ])

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

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_INITCAP('the lake')
FROM DUAL;

Result:

   NLS_INITCAP('THELAKE') 
_________________________ 
The Lake                 

Specify a Collation

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

SELECT 
    NLS_INITCAP('het ijsselmeer') AS c1,
    NLS_INITCAP('het ijsselmeer', 'NLS_SORT = XDutch') AS c2
FROM DUAL;

Result:

               C1                C2 
_________________ _________________ 
Het Ijsselmeer    Het IJsselmeer    

Notice that in the second column, the first two letters of IJsselmeer are capitalised, whereas the first column only capitalises the first letter.

In Dutch, ij is considered as one letter, and so both characters are capitalised when they occur at the beginning of a sentence or a proper name.

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_INITCAP('het IJsselmeer', 'NLS_SORT = XDutch')) 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.

What if I Pass All UPPERCASE Letters?

Passing all uppercase letters doesn’t change the result:

SELECT 
    NLS_INITCAP('HET IJSSELMEER', 'NLS_SORT = XDutch') AS Result
FROM DUAL;

Result:

           RESULT 
_________________ 
Het IJsselmeer    

Null Values

If any argument is null, the result is null:

SET NULL 'null';

SELECT 
    NLS_INITCAP('het IJsselmeer', null) AS r1,
    NLS_INITCAP(null, 'NLS_SORT = XDutch') AS r2,
    NLS_INITCAP(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_INITCAP() without passing any arguments returns an error:

SELECT NLS_INITCAP()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_INITCAP()
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 the wrong number of arguments results in an error:

SELECT NLS_INITCAP('het IJsselmeer', 'NLS_SORT = XDUTCH', 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_INITCAP('het IJsselmeer', 'NLS_SORT = XDUTCH', 3)
FROM DUAL
Error at Command Line : 1 Column : 59
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: