Skip site navigation (1) Skip section navigation (2)

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: (view raw or whole thread)
Lists: pgsql-sql

 i have a table with some text fields filled with a data like
 the same fields somethimes contains the values like
 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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group