Skip to content

Instantly share code, notes, and snippets.

@takmatsukawa

takmatsukawa/psql_naturalsort.sql

Forked from veob/psql_naturalsort
Last active September 25, 2017 00:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save takmatsukawa/07e6352366fb2f465b5ddc9206bcb955 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save takmatsukawa/07e6352366fb2f465b5ddc9206bcb955 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)::text || length(r[1])::text || r[1]),'SQL_ASCII'),'\x00')
from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; $f$;
-- for postgres 8.4
create or replace function naturalsort(text)
returns bytea language sql immutable strict as
$f$ select array_to_string(array_agg(coalesce(r[2],length(length(r[1])::text)::text || length(r[1])::text || r[1])), E'\\000')::bytea
from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r; $f$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment