sorting the text values as integers

From: Sandis Jerics <sandis(at)mediaparks(dot)lv>
To: pgsql-sql(at)postgresql(dot)org
Subject: sorting the text values as integers
Date: 2000-11-10 11:55:18
Message-ID: 3580.001110@mediaparks.lv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

i have a table with some text fields filled with a data like
100,23
235,12
500
200
the same fields somethimes contains the values like
100x100x25
125x125x50
200x80x90
and so on.

the client requires that rows are sorted in ascending order

for the case there are a float values, i do:
SELECT ... ORDER BY float4(field)

for the case there a text values, i do:
SELECT ... ORDER BY int2(substring(field from 1 for position('x' in field)-1));
so i can sort them ascendingly at least by the first integer (before
'x' char). otherwise (simply "ORDER BY field") they were sorted as text
values - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ...
now it sorted as i need - 10x10x10, 40x40x20, 100x100x30, 400x400x30 ...

it's almost fine, but...
now i need to combine that 2 cases, so i try (the field called m1):
SELECT ... ORDER BY (CASE WHEN position('x' in m1)>1 THEN int2(substring(m1 from 1 for position('x' in m1)-1)) ELSE float4(m1) END)

i never used CASE WHEN ... THEN ... ELSE ... END construct before,
& assume the above is errorneus by default.


--:)--
Best regards, Sandis

Browse pgsql-sql by date

  From Date Subject
Next Message Najm Hashmi 2000-11-10 15:59:28 [sql]Joins
Previous Message Kovacs Zoltan Sandor 2000-11-10 11:08:50 Re: Return from stored procedures