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

From: "Adam Haberlach" <adam@newsnipple.com>

> >
> > 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?

It's covered in functions-formatting.htm, basically

richardh=> select to_number('XXX 123 apple','999D99');
 to_number
-----------
       123
(1 row)

Not sure it works the way I think it should, but I'd need to read the docs
more closely.

> > 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.

Ah, but if the regex has proved this field only contains numbers, a simple
cast should do it, or am I missing something?

>
> If all else fails, I'll write the C function.
>
> --
> Adam Haberlach            | All your base are belong to us.
> adam@newsnipple.com       |
> http://www.newsnipple.com |
> '88 EX500    '00 >^<      |

- Richard Huxton