The NLSSORT function returns a string of bytes used to sort a text string. You can use this function to specify sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of a string.

See also:

Oracle Database Globalization Support Guide

Return Value



NLSSORT(char [, 'NLS_SORT = sort[_ai |_ci]' ])



A text expression.


An NLS language.


Specifies an accent-insensitive sort.


Specifies a case-insensitive sort.


Example 8-36 Determining the Bytes by Which Values Are Sorted Bsed on Linguistic Sort Sequence

Assume you want to know the bytes by which a single value is sorted in the German language without regard to case. To do this you can execute the following SHOW command.

show  NLSSORT('Mary Ann' , 'NLS_SORT = German_ai' )

To see this same data for all of the values in a text dimension named myname are sorted, you can execute the following statement.

REPORT NLSSORT(MYname , 'NLS_SORT = German_ci' )
MYNAME           NLSSORT(MYNAME , 'NLS_SORT = German_ci')
---------------- ----------------------------------------
Adelaid          1423284B143C23000101010101010100
Maryann          50146482145555000101010101010100
Mary Ann         501464820114555500010101010201010100
Donna            235A55551400010101010100