CODEWITHSUNDEEP
snowflake-cloud-data-platformnatural-sort
Iulia Marin
Iulia Marin

Reputation: 1

How can I do natural sort in Snowflake?

I am trying to sort Jira stories with several potential structures: 3 to 5 characters followed by dash followed by 3 or 4 digits e.g. XXX-###, XXX-####, XXXX-### etc.

Upvotes: 0

Views: 533

Answers (1)

David Garrison
David Garrison

Reputation: 2880

I'm not quite sure what you mean by a natural sort, but you can sort by a regex. For example, to get just the number of a JiraID string.

--This Returns '123'
select REGEXP_SUBSTR( 'xx-123' , '\\d+');

--This Returns 'xx'
select REGEXP_SUBSTR( 'xx-123' , '\\w+');

--So this would sort by just the number in a Jira number column based on the number part
... order by REGEXP_SUBSTR( JiraID , '\\d+')::int;

--This would order first by IDs with the same string, and then order by Number
... order by REGEXP_SUBSTR( JiraID , '\\w+'), REGEXP_SUBSTR( JiraID , '\\d+')::int;

More complex examples are possible if you provide a sample input with a desired output ordering

Upvotes: 2

Related Questions