Mobile version of this page
Save this page in del.icio.usExploding Dell laptops
Next:
More crappy album covers
Related blogs
PostgreSQL, MySQL or SQLiteAdding a year in PostgreSQL
Creating a user for postgresql
Quick PostgreSQL optimization story
List of casts in PostgreSQL
Running simple SQL commands on the command line
Integer division in programming languages
Date formatting in python or in PostgreSQL
pg_class to check if table exists
Just Oracle and IBM?
ALTER TABLE patch
Date formatting in Python or in PostgreSQL (part II)
Related by category
Sorting transform function in PostgreSQL
sort order, substring, sorting, transform, postgresql, humanreable, human-sorted, extract, identifier
3rd of August 2006
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:
FROM questions
WHERE section=123
ORDER BY identifier
The result you would get is:
Head10
Head2
Head3
...
The human readable sort order should have been this:
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:
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:
Head9c
Head9b
First time it works.