Sorting by numerical order

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:

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

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/

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