NLSSORT

Description

NLSSORT returns a collation key for the character value char and an explicitly or implicitly specified collation. A collation key is a string of bytes used to sort char according to the specified collation. The property of the collation keys is that mutual ordering of two such keys generated for the given collation when compared according to their binary order is the same as mutual ordering of the source character values when compared according to the given collation.. (NLSSORT in Oracle).

Sample Source Pattern

Syntax

Oracle

NLSSORT(char [, 'nlsparam' ])

Snowflake SQL

Snowflake SQL Documentation

COLLATE(<string_expression>, '<collation_specification>')

Examples

Oracle

Code:

CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('Gaasten');

SELECT *
  FROM test
  ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

Result:

NAME            
-------
Gaberd        
Gaardiner
Gaasten     

Snowflake SQL

Code:

CREATE OR REPLACE TABLE test (name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO test
VALUES ('Gaardiner');

INSERT INTO test
VALUES ('Gaberd');

INSERT INTO test
VALUES ('Gaasten');

SELECT *
  FROM
  test
ORDER BY
COLLATE(name, '');

Result:

NAME            
-------
Gaberd        
Gaardiner
Gaasten