[postgis-users] isnumeric function

[Chris Hermansen]

This reminds me of a "funny" bug we have spent the last few months
tracking down.  In Canada we have map tile numbers like 104K045.  Or
more interestingly, 092E004.

Guess what happens then?

It's simply amazing how deeply one can get into a JDBC connection before
that causes a barf.

Michael Toews wrote:
> "Numeric" is an ambiguous qualifier. This really depends on what you define as numeric: integers or reals. Furthermore, if you have negatives, or exponential notation, etc. I'd investigate into regular expressions in general (and also http://www.postgresql.org/docs/current/static/functions-matching.html).
>
> If you want a floating point (and integer) regexp function, try instead:
>
> CREATE OR REPLACE FUNCTION isnumeric(text)
>   RETURNS boolean AS $BODY$SELECT $1 ~ E'^[-+]?\\d*\\.?\\d+(?:[eE][-+]?\\d+)?$'$BODY$
>   LANGUAGE 'sql' IMMUTABLE;
>
> (note: [eE] tests for numbers like '4.3e-32'; also IMMUTABLE is used to increase performance on large queries)
>
> -Mike
>
> ----- Original Message -----
> From: "Alexandre Dube" <adube at mapgears.com>
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Sent: Wednesday, 10 June, 2009 08:21:56 GMT -08:00 US/Canada Pacific
> Subject: Re: [postgis-users] isnumeric function
>
> Hi,
>
>   Still searching for a solution...  Any hint ?
>
> Alexandre
>
> Alexandre Dube wrote:
>   
>> Hi,
>>
>>  I'm looking for an isnumeric() function in postgresql.  I did a quick 
>> research and only found this :
>>
>> http://archives.postgresql.org/pgsql-sql/2004-04/msg00336.php
>>
>>  Is there still no such function in postgresql ?
>>
>>     
>
>
>   


-- 
Regards,

Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5