Re: Isnumeric function? - Mailing list pgsql-sql

<p><font size="2">I was just thinking, wouldn't it be great if the pg community had a site where anyone could
contributetheir generic functions, or request for a particular function.</font><p><font size="2">Cold Fusion has a
cflib.org,perhaps a pglib.org?</font><br /><p><font size="2">-----Original Message-----</font><br /><font
size="2">From:Jeff Eckermann [<a href="mailto:jeff_eckermann@yahoo.com">mailto:jeff_eckermann@yahoo.com</a>] </font><br
/><fontsize="2">Sent: Friday, 10 September 2004 12:02 AM</font><br /><font size="2">To: Thomas Swan;
olly@lfix.co.uk</font><br/><font size="2">Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.org</font><br /><font
size="2">Subject:Re: [SQL] Isnumeric function?</font><br /><p><font size="2">Ok, how about this.  At least it works in
mytesting. </font><br /><font size="2">I have extended it to allow a negative sign (trailing</font><br /><font
size="2">also),which I would expect to be allowed in a</font><br /><font size="2">comprehensive "isnumeric" function. 
IfI am wrong,</font><br /><font size="2">feel free to slap me around; although correcting the</font><br /><font
size="2">regexwould be more constructive. ;-)</font><p><font size="2">create function isnumeric(text) returns boolean
as'</font><br /><font size="2">select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)-?)\'</font><br/><font size="2">'language
'sql';</font><p><fontsize="2">--- Thomas Swan <tswan@idigx.com> wrote:</font><p><font size="2">> Oliver
Elphickwrote:</font><br /><font size="2">> </font><br /><font size="2">> >On Wed, 2004-09-08 at 18:48, Josh
Berkuswrote:</font><br /><font size="2">> >  </font><br /><font size="2">> ></font><br /><font
size="2">>>>Theo, Oliver,</font><br /><font size="2">> >></font><br /><font size="2">> >>   
</font><br/><font size="2">> >></font><br /><font size="2">> >>>Any reason why you don't like 
~</font><br/><font size="2">> '^([0-9]?)+\.?[0-9]*$' ?</font><br /><font size="2">> >>>      </font><br
/><fontsize="2">> >>></font><br /><font size="2">> >>Yes, because it also matches "." , which is
nota</font><br /><font size="2">> valid numeric value.</font><br /><font size="2">> >></font><br /><font
size="2">>>>    </font><br /><font size="2">> >></font><br /><font size="2">> >>> ~
'^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'</font><br/><font size="2">> >>>      </font><br /><font
size="2">>>>></font><br /><font size="2">> >>Ah, the brute force approach ;-)</font><br /><font
size="2">>>>    </font><br /><font size="2">> >></font><br /><font size="2">> ></font><br
/><fontsize="2">> >Nothing like using a nice big hammer!</font><br /><font size="2">> ></font><br /><font
size="2">>>  </font><br /><font size="2">> ></font><br /><font size="2">> Would
"^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$"be a</font><br /><font size="2">> little cleaner?</font><br /><font
size="2">></font><br /><font size="2">> >>Actually, the above could be written:</font><br /><font
size="2">>>></font><br /><font size="2">> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'</font><br /><font
size="2">>>>    </font><br /><font size="2">> >></font><br /><font size="2">> ></font><br
/><fontsize="2">> >But that doesn't allow a trailing decimal point.</font><br /><font size="2">>
></font><br/><font size="2">> >  </font><br /><font size="2">> ></font><br /><font size="2">>
>>...though that still seems inelegant to me.  Is</font><br /><font size="2">> there a regex expert in
the</font><br/><font size="2">> >>house?</font><br /><font size="2">> >>    </font><br /><font
size="2">>>></font><br /><font size="2">> ></font><br /><font size="2">> >All the elegant
approachesI can think of match the</font><br /><font size="2">> empty string. There</font><br /><font size="2">>
>mustbe at least one digit and 0 or 1 decimal point</font><br /><font size="2">> with no other</font><br /><font
size="2">>>characters permitted.  If you use this as a</font><br /><font size="2">> constraint, you could
make</font><br/><font size="2">> >it elegant and combine it with another constraint</font><br /><font
size="2">>to exclude '' and '.'.</font><br /><font size="2">> >  </font><br /><font size="2">>
></font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">>
---------------------------(endof</font><br /><font size="2">> broadcast)---------------------------</font><br
/><fontsize="2">> TIP 6: Have you searched our list archives?</font><br /><font size="2">> </font><br /><font
size="2">>               <a href="http://archives.postgresql.org"
target="_blank">http://archives.postgresql.org</a></font><br/><font size="2">> </font><br /><br /><p>               
<br/><font size="2">__________________________________</font><br /><font size="2">Do you Yahoo!?</font><br /><font
size="2">Yahoo!Mail - 50x more storage than other providers! <a href="http://promotions.yahoo.com/new_mail"
target="_blank">http://promotions.yahoo.com/new_mail</a></font><table><tr><tdbgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/>This email, including
attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If
you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table>