How to convert integer to string in functions

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