Sorting transform function in PostgreSQL

03 August 2006   4 comments   Work

Powered by Fusion×

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
Thank you for posting a comment

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 keywords:
Fastest database for Tornado 09 October 2013
Integer division in programming languages 04 August 2004
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Connecting with psycopg2 without a username and password 24 February 2011
Adding a year in PostgreSQL 04 February 2004
pg_class to check if table exists 20 April 2005
To sub-select or not sub-select in PostgreSQL 31 August 2009
Speed test between django_mongokit and postgresql_psycopg2 09 March 2010
UPPER vs. ILIKE 19 April 2010
Date formatting in python or in PostgreSQL 20 July 2004
PostgreSQL, MySQL or SQLite 04 April 2004
Postgres collation errors on CITEXT fields when upgrading to 9.1 21 May 2012