Re: ...lame use of casting, looking for workaround...

On Sat, Feb 24, 2001 at 11:16:49AM +0000, Richard Huxton wrote:
> 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.

I couldn't figure out how to use to_number from the documentation (I'm
beginning to think I should join the "more docs now" chant). Can someone
tell me how I would find the first number in a text field using to_number?

> 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've got a regex that will do that, but that, as far as I know, only
checks for matches, and won't provide the numbers themselves so we can
put them in a BETWEEN clause.

If all else fails, I'll write the C function.

--
Adam Haberlach | All your base are belong to us.
adam(at)newsnipple(dot)com |
http://www.newsnipple.com |
'88 EX500 '00 >^< |