Sorting transform function in PostgreSQL

03 August 2006   5 comments   Work

Mind That Age!

This blog post is 12 years old! Most likely, its content is outdated. Especially if it's technical.

A database table that I've had to work with has a something called identifiers which are humanreable names of questions. There's a HTML template where the question designer can place the various questions in a human-sorted order. All questions get identifiers in the form of <something><number> like this: Head1 or Soma3 or Reg10 where Head, Soma and Reg are sections. Changing the nameing convention from Head1 to Head01 is too late because all the templates already expect Head1 not Head01. Initially I sorted the identifiers like this:

SELECT id, identifier 
FROM questions
WHERE section=123
ORDER BY identifier 

The result you would get is:

Head1
Head10
Head2
Head3
...

The human readable sort order should have been this:

Head1
Head2
Head3
...
Head10

To solve this now all I needed to do was to extract the two digit part and cast it as an integer. Like this:

SELECT id, identifier,
  SUBSTRING(identifier FROM '[0-9]{1,2}')::INT AS identifier_int
FROM questions
WHERE section=123
ORDER BY identifier_int, identifier

The reason I who a second order by key is because some identifiers look like this:

Head9a
Head9c
Head9b

Comments

Peter Bengtsson

First time it works.

Peter Bengtsson

A second time. It will then work!

Peter Bengtsson

This time I can get it wrong if I want to.

Anonymous

No need to put it in the results:

SELECT id, identifier,
FROM questions
WHERE section=123
ORDER BY SUBSTRING(identifier FROM '[0-9]{1,2}')::INT, identifier

Valerii

CREATE OR REPLACE FUNCTION human_sort(text)
  RETURNS text[] AS
$BODY$
  /* Split the input text into contiguous chunks where no numbers appear,
     and contiguous chunks of only numbers. For the numbers, add leading
     zeros to 20 digits, so we can use one text array, but sort the
     numbers as if they were big integers.

       For example, human_sort('Run 12 Miles') gives
            {'Run ', '00000000000000000012', ' Miles'}
  */
  select array_agg(
    case
      when a.match_array[1]::text is not null
        then a.match_array[1]::text
      else lpad(a.match_array[2]::text, 20::int, '0'::text)::text
    end::text)
    from (
      select regexp_matches(
        case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'
      ) AS match_array
    ) AS a
$BODY$
  LANGUAGE sql IMMUTABLE;

SELECT *
FROM "questions"
ORDER BY human_sort("identifier")


https://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings

Your email will never ever be published


Related posts

Previous:
Exploding Dell laptops 31 July 2006
Next:
More crappy album covers 06 August 2006
Related by Keyword:
How I performance test PostgreSQL locally on macOS 10 December 2018
Best EXPLAIN ANALYZE benchmark script 19 April 2018
When Docker is too slow, use your host 11 January 2018
How do log ALL PostgreSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013
Related by Text:
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
Ever wondered how much $87 Billion is? 04 November 2003
Guake, not Yakuake or Yeahconsole 23 January 2010