Snowflake: split string into array, sort it, and return string.

Related links: 

• Snowflake: Semi-structured Data Functions

PROBLEM:
Receive a delimited string, convert it to Snowflake array, sort the array, and return string back.

SOLUTION: 

Build a custom function called ARRAY_SORT. The function can have optional DELIMITER parameter. E.g. the function is overloaded. 

---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION MAINDB..array_sort(A array)
  RETURNS string
  LANGUAGE JAVASCRIPT
COMMENT = 'Sort array passed as parameter. Return COMMA delimited string.'
AS
$$
  return A.sort();
$$
;
------- Overload: optional DELIM argument ---------------------------

CREATE OR REPLACE FUNCTION MAINDB..array_sort(A array, DELIM varchar )
  RETURNS string
  LANGUAGE JAVASCRIPT
COMMENT = 'Sort array passed as parameter. Return SPECIFIED delimiter string.'
AS
$$
  return (A.sort()).join(DELIM);
$$
;
---------------------------------------------------------------------
GRANT USAGE  ON FUNCTION  MAINDB..ARRAY_SORT(ARRAY)          TO PUBLIC; 
GRANT USAGE  ON FUNCTION  MAINDB..ARRAY_SORT(ARRAY, VARCHAR) TO PUBLIC; 

me#WH_XS@MAINDB.PUBLIC>show functions like 'ARRAY_SORT%' in database MAINDB;
+-------------------------------+------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------+----------------------------------------------------------------------+--------------+-------------------+----------------------+-----------+
| created_on                    | name       | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments | arguments                                 | description                                                          | catalog_name | is_table_function | valid_for_clustering | is_secure |
|-------------------------------+------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------+----------------------------------------------------------------------+--------------+-------------------+----------------------+-----------|
| 2019-09-09 13:44:55.919 -0700 | ARRAY_SORT | PUBLIC      | N          | N            | N       |                 1 |                 1 | ARRAY_SORT(ARRAY) RETURN VARCHAR          | Sorts array passed as argument returning COMMA delimited string.     | MAINDB       | N                 | N                    | N         |
| 2019-09-09 13:45:04.973 -0700 | ARRAY_SORT | PUBLIC      | N          | N            | N       |                 2 |                 2 | ARRAY_SORT(ARRAY, VARCHAR) RETURN VARCHAR | Sorts array passed as argument returning SPECIFIED delimiter string. | MAINDB       | N                 | N                    | N         |
+-------------------------------+------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------+----------------------------------------------------------------------+--------------+-------------------+----------------------+-----------+

USAGE EXAMPLE:

select  
     a.tag                                                        as STRING_TAG_AS_IS
   , STRTOK_TO_ARRAY(a.tag,CHR(182))                              as ARRAY_TAG
   , MAINDB..array_sort(STRTOK_TO_ARRAY(a.tag, CHR(182) ))          as STRING_TAG_SORTED
   , MAINDB..array_sort(STRTOK_TO_ARRAY(a.tag, CHR(182) ),CHR(182)) as STRING_TAG_SORTED_DELIM
FROM  somedb..sometable  a
limit 30;

• Also ref: STRTOK_TO_ARRAY and CHR