Thread: Sorting by numerical order

Is it possible to have items sorted like:

abc 1
abc 2
abc 10
abc 20

instead of:

abc 1
abc 10
abc 2
abc 20

?

Thanks in advance,
CSN

__________________________________
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
http://mail.yahoo.com

CSN wrote:
> Is it possible to have items sorted like:
>
> abc 1
> abc 2
> abc 10
> abc 20

Cast them to a numeric type.

> CSN wrote:
> > Is it possible to have items sorted like:
> >
> > abc 1
> > abc 2
> > abc 10
> > abc 20
>
> Cast them to a numeric type.

You can't cast a variable char type to a numeric, though apparently
you can cast text to numeric.  (I'd be curious to know why one but not
the other.)

However, if I understood the original question, 'abc 1' and 'abc 20' are
contents of a single column, which might make separating out just the
numeric part tricky.

On a somewhat related subject, how difficult would it be to write an
'isnumeric' function as part of the source code as opposed to a user
function in pgsql or perl?

I have some data that is mostly numeric but not always so.  I know from
other columns whether a particular row is supposed to contain a numeric
value or not, but the data is coming from a legacy environment
and isn't 100% reliable.

An 'isnumeric(column)' boolean function in a case statment would be
very helpful so that I can program around the bad data.

I'll probably write it in perl, but would making it part of the source
code provide significantly faster execution?
--
Mike Nolan

On Sun, Mar 14, 2004 at 04:59:45PM -0600, Mike Nolan wrote:
> On a somewhat related subject, how difficult would it be to write an
> 'isnumeric' function as part of the source code as opposed to a user
> function in pgsql or perl?

Regular expressions? Something like:

column ~ '^[-+]?[0-9]+(\.[0-9]+)?$'

Should do the trick. Ofcourse, if you're dealing with exponentials
you'll need to add more, browsing the web should give you some
examples.

I don't see writing it in C being significantly faster, but it's
possible I guess.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

On Sun, Mar 14, 2004 at 04:59:45PM -0600, Mike Nolan wrote:

> An 'isnumeric(column)' boolean function in a case statment would be
> very helpful so that I can program around the bad data.
>
> I'll probably write it in perl, but would making it part of the source
> code provide significantly faster execution?

Couldn't you just use a regex match? foo ~ '-?[0-9]+(\.[0-9]+)?' or
somesuch.

Cheers,
  Steve

Mike Nolan <nolan@gw.tssi.com> writes:
> You can't cast a variable char type to a numeric, though apparently
> you can cast text to numeric.  (I'd be curious to know why one but not
> the other.)

Lack of the necessary entries in pg_cast.  I can't think of any
particularly good reason why they're not there, so I have added them in
CVS tip.

            regards, tom lane

> Regular expressions? Something like:
>
> column ~ '^[-+]?[0-9]+(\.[0-9]+)?$'

Oh, that's too easy.  Duh!  (Coming from years in an Oracle 7 environment,
I sometimes forget regex are there, I guess.)
--
Mike Nolan

On Sun, Mar 14, 2004 at 04:59:45PM -0600, Mike Nolan wrote:
> > CSN wrote:
> > > Is it possible to have items sorted like:
> > >
> > > abc 1
> > > abc 2
> > > abc 10
> > > abc 20
> >
> > Cast them to a numeric type.
>
> You can't cast a variable char type to a numeric, though apparently

 You can:

test=# select to_number('abc 1', '"abc "99');
 to_number
-----------
         1
(1 row)

test=# select to_number('abc 20', '"abc "99');
 to_number
-----------
        20
(1 row)


    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Steve Atkins wrote:
>
> Couldn't you just use a regex match? foo ~ '-?[0-9]+(\.[0-9]+)?' or
> somesuch.
>
> Cheers,
>  Steve

This helps a lot.  I used a similar regular expression, the substring function, and the order by
clause to do the following:

test=# select * from sort_test order by name;
  name
--------
 10
 aaa
 abc
 abc 1
 abc 10
 abc 2
 abc 20
(7 rows)

test=# select name, substring(name from '^(.*?)([-+]?[0-9]+(\\.[0-9]+)?)?$') as start_string,
substring(name from '[-+]?[0-9]+(\\.[0-9]+)?$')::float as end_number from sort_test order by
start_string, end_number;
  name  | start_string | end_number
--------+--------------+------------
 10     |              |         10
 aaa    | aaa          |
 abc    | abc          |
 abc 1  | abc          |          1
 abc 2  | abc          |          2
 abc 10 | abc          |         10
 abc 20 | abc          |         20
(7 rows)

George Essig