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.
[EMAIL PROTECTED]       |
http://www.newsnipple.com |
'88 EX500    '00 >^<      |

Reply via email to