Re: to_number behavior change between 8.1 and 8.2

On 5/16/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> Richard Greenwood wrote:
> > I am getting an error from to_number() in PostgreSQL 8.2 that does not
> > occur in 8.1:
> > invalid input syntax for type numeric: " "
> > I assume that it is rejecting spaces, so I tried trimming with:
> > to_number(trim (both ' ' from streetnum),'999999999')
> > but still get the same error.
>
> In 8.2 we no longer accept anything but valid numbers for numbers :). ""
> is not a valid number.
>
> The way around this is to change "" to NULL .
>
> Sincerely,
>
> Joshua D. Drake

Thanks for the replies. The old behavior was certainly handier for my
application.

I had been using the old to_number() to get a numeric sort on a char
field of street address "numbers" that contained spaces and "numbers"
like '502B'. So with 8.2, I could:
1. pre-process (clean up) the data so that it really was numeric
2. write my own function duplicating the 8.1 behavior
3. or, any other ideas?

Thanks again,
Rich

--
Richard Greenwood
richard(dot)greenwood(at)gmail(dot)com
www.greenwoodmap.com