2012/3/20 Chris Angelico <rosuav@gmail.com>:
> On Tue, Mar 20, 2012 at 7:14 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> But this only works if the input is a clean list of number characters already!
>> Anything other than this will issue an error:
>>
>> postgres=# SELECT '10'::int;
>>
>> After trying hard to cope with anything possibly as an input string I
>> found this:
>>
>> postgres=# SELECT to_number('0'||mytextcolumn,
>> '99999999999.000')::int FROM mytable;
>
> I came across the same issue, specifically wanting semantics like C's
> atoi function. Some discussion on this list turned up a few options.
>
> Beginning of thread, including one possibility:
> http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00090.html
> Another well-researched option, with slightly different semantics:
> http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00094.html
>
> Hope that helps!
>
> ChrisA
Referring to your last hint, this is was Tom's answer:
> Can you use to_number() here? It sounds like something along the lines of
> cast(to_number('0' || field::varchar, '999999999.') as int)
> might give the behaviour you're after, and a quick test seems to indicate
> that it's about 4x faster than the original function:
I'm actually flattered that I came across almost the same solution as
Tom with my proposal:
to_number('0'||mytextcolumn, '99999999999.000')::int
> ...
> Hopefully there's a cleaner way of writing that without a long list of 9s in the
> format string, and if the field is nullable I'd guess you probably need a
> coalesce(..., 0) around that as well.
Would be glad to find any cleaner way but that's the silver bullet until then:->
-Stefan
