NLSSORT Oracle to Snowflake

Question

I'm trying to convert the following code from Oracle to Snowflake:

order by nlssort(name, 'NLS_SORT=BINARY')

I know NLSSORT is not a function in Snowflake, but is there anything I can use as an alternative?

Answer 1

It should be pretty similar already to Snowflake's default sorting - you just need to consider your database charset in Oracle (select * from nls_database_parameters where parameter='NLS_CHARACTERSET') and see whether it has a different binary order than ASCII/UTF-8.

Oracle's documentation:

If the value is BINARY, then comparison is based directly on byte values in the binary encoding of the character values being compared.

Snowflake's documentation:

All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported.

So I think you should be able to just do:

order by name

It's kind of odd that somebody would write that Oracle code to begin with, since BINARY is the default sort order (collation). But if your Oracle database is using multilingual collation (which is not common) for other queries, I don't think you're going to be able to easily emulate that in Snowflake.