Re: Isnumeric function?

I was just thinking, wouldn't it be great if the pg community had a site
where anyone could contribute their generic functions, or request for a
particular function.

Cold Fusion has a cflib.org, perhaps a pglib.org?

-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann(at)yahoo(dot)com]
Sent: Friday, 10 September 2004 12:02 AM
To: Thomas Swan; olly(at)lfix(dot)co(dot)uk
Cc: Josh Berkus; Theo Galanakis; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Isnumeric function?

Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)

create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';

--- Thomas Swan <tswan(at)idigx(dot)com> wrote:

> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.