List sorting in Snowflake as part of the select

user_9683133 0
Viewed 237

lets assume im having to following query

select column1, count(*) from (values ('a'), ('b'), ('ab'), ('ba')) group by 1;

COLUMN1 COUNT(*)
a   1
b   1
ba  1
ab  1

and I want that my grouping will be order insensitive means that I want to count ab and ba as the same value. so the expected result will be

COLUMN1 COUNT(*)
a   1
b   1
ab  2

I thought about sorting the select so it will handle them both as the same value but I didnt find any option to sort the value in snowflake.

snowflake-cloud-data-platform
1 Answers

There may be a way to do this in a SQL, but a JavaScript UDF makes it easy:

create or replace function SORT_STRING(TEXT string)
returns string
language javascript
strict immutable
as
$$
    return TEXT.split('').sort().join('');
$$;

select SORT_STRING(column1) SORTED_C1, count(*) 
from (values ('a'), ('b'), ('ab'), ('ba')) group by 1;
SORTED_C1 COUNT(*)
a 1
b 1
ab 2
user_127563810
Related