Thread: How to convert integer to string in functions
Hi All,
In my table, some of the columns are in text datatype. Few data will come down from UI layer as integers. I want to convert that to string/text before saving it into the table. Please help me on this.
Thanks and Regards,
Siva.
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
In my table, some of the columns are in text datatype. Few data will come down from UI layer as integers. I want to convert that to string/text before saving it into the table. Please help me on this.
SQL Standard: "CAST( value AS text )" [or varchar]
PostgreSQL short-hand: "value::text"
In both formats replace value with whatever you want to convert. When writing a parameterized query (using ?) you can write "?::text" ( or Cast(? AS type) ) to explicitly cast the unknown parameter. The "text" in the above can be any type name.
David J.
On Friday, August 12, 2011 6:53:57 am Siva Palanisamy wrote: > Hi All, > > In my table, some of the columns are in text datatype. Few data will come > down from UI layer as integers. I want to convert that to string/text > before saving it into the table. Please help me on this. Should not need to. String into integer would be a problem, but not integer into string. > > Thanks and Regards, > Siva. -- Adrian Klaver adrian.klaver@gmail.com
Hi,
2011/8/12 David Johnston <polobo@yahoo.com>:
> In my table, some of the columns are in text datatype. Few data will come
> down from UI layer as integers. I want to convert that to string/text before
> saving it into the table. Please help me on this.
>
>
> SQL Standard: "CAST( value AS text )" [or varchar]
> PostgreSQL short-hand: "value::text"
>
> In both formats replace value with whatever you want to convert. When
> writing a parameterized query (using ?) you can write "?::text" ( or Cast(?
> AS type) ) to explicitly cast the unknown parameter. The "text" in the
> above can be any type name.
>
> David J.
You often find this advice of doing a cast.
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;
You can try this here: Show all peaks of Switzerland which are higher
than 4000 meters above sea.
SELECT ST_AsText(way) AS geom, name||','||ele AS label
FROM osm_point
WHERE "natural" = 'peak'
AND to_number('0'||ele, '99999999999.000')::int >= 4000
Any better solutions are welcome.
Yours, Stefan
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
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