In Oracle, the NLS_LOWER() function returns its argument with all letters in lowercase.

It’s similar to the LOWER() 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_LOWER(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_LOWER('HOMER SYMPTOM') AS Result
FROM DUAL;

Result:

          RESULT 
________________ 
homer symptom   

The same applies when the argument uses mixed case:

SELECT NLS_LOWER('Homer Symptom') AS Result
FROM DUAL;

Result:

          RESULT 
________________ 
homer symptom   

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

SELECT NLS_LOWER('homer symptom') AS Result
FROM DUAL;

Result:

          RESULT 
________________ 
homer symptom   

Specify a Collation

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

SELECT 
    NLS_LOWER('BALIQ') AS r1,
    NLS_LOWER('BALIQ', 'NLS_SORT = XAZERBAIJANI') AS r2
FROM DUAL;

Result:

      R1       R2 
________ ________ 
baliq    balıq   

Notice that the second column uses a dotless lowercase 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_LOWER('BALIQ', '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_LOWER(null, 'NLS_SORT = XAZERBAIJANI') AS r1,
    NLS_LOWER('BALIQ', null) AS r2,
    NLS_LOWER(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_LOWER() without passing any arguments returns an error:

SELECT NLS_LOWER()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLS_LOWER()
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_LOWER('Homer', 'NLS_SORT = XAZERBAIJANI', 'Symptom')
FROM DUAL;

Result:

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