Grouping Numbers in Snowflake – Part 2
In a previous post, I showed a way to format numbers using local grouping characters. One of the advantages of the approach is it will work everywhere across Snowflake since it does not require creation of a User Defined Function (UDF).
Using a UDF provides a lot more power and flexibility, so let’s see how we do that. First, you need to be working with a specific Snowflake Database and have an active Data Warehouse to do any processing including creation of a UDF.
use database TEST_DATABASE;
use warehouse TEST_WAREHOUSE;
Next we need to create the User Defined Function (UDF):
--Return a formatted number with locale-specific grouping and decimal characters.
create or replace function FormatNumber(d double)
returns string
language javascript
strict
as '
if (!isNaN(D)){
return D.toLocaleString("en-US");
} else {
return "Not a number.";
}
';
Of course, we could create a much more sophisticated function with overloads that handles optional local input, etc., but for the purposes of this function that’s overkill. If we need to adjust the locale, we can do that easily in the function by changing the parameter sent to the “toLocaleString”. Note that if you omit this parameter, the function will use the default for the current machine. In my case, the machine is in the US-East region of AWS, so it’s already set to US English, “en-US”, but to be on the safe side, it’s best to specify the right value.
To test the function, we can issue this SnowSQL statement (note that we need to be using the same database as the one where we created the UDF):
--Optionally set the database context if it's changed.
use database TEST_DATABASE;
select formatnumber(123456789.012) as FORMATTED_NUMBER;
When the locale specified US English, “us-EN”, it will return the following:
To get a list of possible values for the locale parameter worldwide, refer to the Mozilla Developer Network documentation here: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/toLocaleString
One of the options on that page is for Han Chinese. I don’t know how to read that, but when I set the locale in the Snowflake UDF to zh-Hans-CN-u-nu-hanidec I’ll assume it’s working just fine when it returns the following result:
Perhaps someone who can read Han Chinese can confirm this for me.
