NLSSORT

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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