Skip to content

Instantly share code, notes, and snippets.

@hevertonfreitas

hevertonfreitas/psql_naturalsort

Forked from veob/psql_naturalsort
Created August 23, 2017 13:02
Show Gist options
  • Save hevertonfreitas/3beb89080b9d28e13f2ddf928f570c12 to your computer and use it in GitHub Desktop.
Save hevertonfreitas/3beb89080b9d28e13f2ddf928f570c12 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL natural sort
Raw
psql_naturalsort
//sql
create or replace function naturalsort(text)
returns bytea language sql immutable strict as
$f$ select string_agg(convert_to(coalesce(r[2],length(length(r[1])::text) || length(r[1])::text || r[1]),'SQL_ASCII'),'\x00')
from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; $f$;
//author: github.com/RhodiumToad
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment