NLSSORT

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.

Return Value

RAW

Syntax

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

Parameters

char: A text expression.
sort: An NLS language.
_ai: Specifies an accent-insensitive sort.
_ci: Specifies a case-insensitive sort.

Examples

Example 8-36 Determining the Bytes by Which Values Are Sorted Based 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' )
501464820114555500010101010201010100

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