Oracle NLS_INITCAP() Function

Oracle NLS_INITCAP() is a built-in function that converts the first letter of each word in the given string to uppercase and the other letters to lowercase.

Oracle NLS_INITCAP() syntax

Here is the syntax for the Oracle NLS_INITCAP() function:

NLS_INITCAP(str [, 'nlsparam' ])

Parameters

str

Required. It can be any data type in CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The function does not directly support CLOB data. However, CLOB values ​​can be passed in as parameters through implicit data conversion.

'nlsparam'

Optional. You can set this parameter using the form 'NLS_SORT = sort', where sort is the name of the collation. Collations handle language-specific requirements for case conversion. If you omit this parameter, the collation is determined by the function.

Return Value

The Oracle NLS_INITCAP() function returns a string that converts the first letter of each word in the string specified by the parameter to uppercase, and converts other letters to lowercase.

If any parameter is NULL, NLS_INITCAP() will return NULL.

Oracle NLS_INITCAP() Examples

Here are some examples that demonstrate the usage of the Oracle NLS_INITCAP() function.

Basic Usage

INITCAP() is ame as NLS_INITCAP() without 'nlsparam' parameter.

SELECT
    INITCAP('hello world') "INITCAP('hello world')",
    NLS_INITCAP('hello world') "NLS_INITCAP('hello world')"
FROM dual;

Output:

INITCAP('hello world')    NLS_INITCAP('hello world')
_________________________ _____________________________
Hello World               Hello World

Collation

Oracle NLS_INITCAP() function allows you to specify collations to handle language-specific rules. For example, in the Dutch language, ij is one letter, and it should be converted to uppercase IJ instead I. Take a look at the example below:

SELECT
    NLS_INITCAP('ijsland') "Result1",
    NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "Result2"
FROM dual;

Output:

Result1    Result2
__________ __________
Ijsland    IJsland

NULL Parameters

If any parameter is NULL, NLS_INITCAP() will return NULL.

SET NULL 'NULL';
SELECT
    NLS_INITCAP(NULL)
FROM dual;

Output:

NLS_INITCAP(NULL)
________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle NLS_INITCAP() is a built-in function that converts the first letter of each word in the given string to uppercase and the other letters to lowercase.