X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 569DC9254DD for ; Mon, 19 May 2003 21:32:32 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 27448-04 for ; Mon, 19 May 2003 21:31:59 -0400 (EDT) Received: from fw1.ultimeth.net (bdsl.66.13.45.202.gte.net [66.13.45.202]) by developer.postgresql.org (Postfix) with ESMTP id BC9E59257CA for ; Mon, 19 May 2003 20:28:10 -0400 (EDT) Received: from TpA22p1.ultimeth.net ([192.168.128.199]) (authenticated) by fw1.ultimeth.net (8.11.6/8.11.6) with ESMTP id h4K0S9401081 for ; Mon, 19 May 2003 17:28:10 -0700 Message-Id: <5.1.0.14.2.20030519171925.00ad4db0@imaps.ultimeth.net> X-Sender: mailer@dba-sql.ultimeth.net (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Mon, 19 May 2003 17:28:05 -0700 To: pgsql-sql@postgresql.org From: "Dean Gibson (DB Administrator)" Subject: Re: Testing castability of text to numeric In-Reply-To: References: <3EC4B2BA.B872FCDF@rodos.fzk.de> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_551273449==_.ALT" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/377 X-Sequence-Number: 13542 --=====================_551273449==_.ALT Content-Type: text/plain; charset="us-ascii" Try something like this: numeric_column = CASE WHEN trim( text_column, '0123456789' ) = '' THEN text_column::NUMERIC ELSE NULL END If your text_column also has blanks (leading or trailing), you may want to adapt the above slightly, but I think you get the idea. I don't know how to do arbitrarily formatted dates. -- Dean Achilleus Mantzios wrote on 2003-05-16 07:57: >On Fri, 16 May 2003, Christoph Haller wrote: > >> > >> > I am trying to create a function that will test if a given text value >> > may safely be cast to numeric, returning the numeric cast or null if >> > impossible. > >I think that progress is being made on >having java as a serverside language >(i.e. plpgjava). >Stored procedures in java would be really handy in order >to do staff like that, java is known for its >"exceptional" exception handling! > >What language do you use to bould your applications?? > >> > >> > Is there a way to "catch" the "ERROR: Bad numeric input format" >> error? >> > >> > I ask that rather than about what the numeric format is for >> regex-style >> > testing because if this works I may want to use a similar method to >> > check if a given text value casts safely to other types, like date. >> > >> I don't know of any way to catch errors. >> And I can't see any way how to test castability via regex. Can you? >> The only way I can think of at the moment is writing a C function. >> >> Regards, Christoph >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > >-- >================================================================== >Achilleus Mantzios >S/W Engineer >IT dept >Dynacom Tankers Mngmt >Nikis 4, Glyfada >Athens 16610 >Greece >tel: +30-210-8981112 >fax: +30-210-8981877 >email: achill@matrix.gatewaynet.com > mantzios@softlab.ece.ntua.gr > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster --=====================_551273449==_.ALT Content-Type: text/html; charset="us-ascii" Try something like this:

numeric_column = CASE
  WHEN trim( text_column, '0123456789' ) = '' THEN
    text_column::NUMERIC
  ELSE
    NULL
  END

If your text_column also has blanks (leading or trailing), you may want to adapt the above slightly, but I think you get the idea.

I don't know how to do arbitrarily formatted dates.

-- Dean

Achilleus Mantzios wrote on 2003-05-16 07:57:
On Fri, 16 May 2003, Christoph Haller wrote:

> >
> > I am trying to create a function that will test if a given text value
> > may safely be cast to numeric, returning the numeric cast or null if
> > impossible.

I think that progress is being made on
having java as a serverside language
(i.e. plpgjava).
Stored procedures in java would be really handy in order
to do staff like that, java is known for its
"exceptional" exception handling!

What language do you use to bould your applications??

> >
> > Is there a way to "catch" the "ERROR:  Bad numeric input format"
> error?
> >
> > I ask that rather than about what the numeric format is for
> regex-style
> > testing because if this works I may want to use a similar method to
> > check if a given text value casts safely to other types, like date.
> >
> I don't know of any way to catch errors.
> And I can't see any way how to test castability via regex. Can you?
> The only way I can think of at the moment is writing a C function.
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--=====================_551273449==_.ALT--