Adam Haberlach wrote:
>
> One of the rocket scientests working on this project has
> created a system in which /either/ a description or a build
> number is stored in a text field. He has been writing reports
Get that man a job on son of star-wars!
> that use CAST AS('description', int4) to convert the field into
> either 0 or a number. It looks like 7.1beta4 doesn't like this
> silly hack, since I get pg_atoi errors when the query hits
> anything that doesn't consist entirely of a number (and rightly
> so).
Well, to_number does you no good since it seeks out the first thing that
can be used as a number in a piece of text.
So - either use pattern matching to select-out numbers before using
cast, or write yourself a function that checks whether the text starts
with digits before casting. You can write functions in C or plpgsql for this.
>
> I'm looking for a workaround that will let him do his
> "conversion" transparently: some form of atoi that will return
> 0 if it finds no numbers instead of blowing up. Anyone got
> one?
- Richard Huxton