Sort each character in a string from a specific column in Snowflake SQL-CodePudding

I am trying to alphabetically sort each value in a column with Snowflake. For example I have:

| NAME |
| ---- |
| abc |
| bca |
| acb |

and want

| NAME |
| ---- |
| abc |
| abc |
| abc |

how would I go about doing that? I've tried using SPLIT and the ordering the rows, but that doesn't seem to work without a specific delimiter.

CodePudding user response：

Using REGEXP_REPLACE to introduce separator between each character, STRTOK_SPLIT_TO_TABLE to get individual letters as rows and LISTAGG to combine again as sorted string:

SELECT tab.col, LISTAGG(s.value) WITHIN GROUP (ORDER BY s.value) AS result
FROM tab
, TABLE(STRTOK_SPLIT_TO_TABLE(REGEXP_REPLACE(tab.col, '(.)', '\\1~'), '~')) AS s
GROUP BY tab.col;

For sample data:

CREATE OR REPLACE TABLE tab
AS
SELECT 'abc' AS col UNION
SELECT 'bca' UNION
SELECT 'acb';

Output:

CodePudding user response：

Similar implementation as Lukasz's, but using regexp_extract_all to extract individual characters in the form of an array that we later split to rows using flatten . The listagg then stitches it back in the order we specify in within group clause.

with cte (col) as
(select 'abc' union
 select 'bca' union
 select 'acb')


select col, listagg(b.value) within group (order by b.value) as col2
from cte, lateral flatten(regexp_extract_all(col,'.')) b
group by col;