Do you train Kung Fu?
Or know someone who does?
Then check out KungFuPeople.com
Mobile version of this pageExploding Dell laptops
Next:
More crappy album covers
Related blogs
PostgreSQL, MySQL or SQLiteCreating a user for postgresql
Adding a year in PostgreSQL
Date formatting in python or in PostgreSQL
Integer division in programming languages
Running simple SQL commands on the command line
pg_class to check if table exists
List of casts in PostgreSQL
Just Oracle and IBM?
ALTER TABLE patch
Quick PostgreSQL optimization story
Date formatting in Python or in PostgreSQL (part II)
Why bother with MySQL...
To sub-select or not sub-select in PostgreSQL
Speed test between django_mongokit and postgresql_psycopg2
UPPER vs. ILIKE
Fastest "boolean SQL queries" possible with Django
Optimization of getting random rows out of a PostgreSQL in Django
Connecting with psycopg2 without a username and password
Related by category
Work
Sorting transform function in PostgreSQL
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
Comment
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
First time it works.