Re: Isnumeric function? - Mailing list pgsql-sql
From | Passynkov, Vadim |
---|---|
Subject | Re: Isnumeric function? |
Date | |
Msg-id | C8C8E7457059D5119E4700D0B765DCB8016AA8D1@sinope.inside.pathcom.com Whole thread Raw |
In response to | Isnumeric function? (Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>) |
List | pgsql-sql |
How about this CREATE OR REPLACE FUNCTION is_numeric ( text ) RETURNS bool AS ' if { [string is integer $1] || [string is double $1] } { return true } return false ' LANGUAGE 'pltcl' IMMUTABLE; SELECT is_numeric ( '-1' );is_numeric ------------t (1 row) SELECT is_numeric ( '+1e-1' );is_numeric ------------t (1 row) SELECT is_numeric ( '1.1.1' );is_numeric ------------f (1 row) -- Vadim Passynkov -----Original Message----- From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] Sent: Thursday, September 09, 2004 10:02 AM To: Thomas Swan; olly@lfix.co.uk Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.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@idigx.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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)