Postgres Pro
Downloads
Home   >   mailing lists

Thread: cast numeric to char

cast numeric to char

From
"Johnson, Shaunn"
Date:

Howdy:

Running PostgreSQL 7.2.1 -

I'm trying to cast a numeric field to character, but I
am getting errors saying that it can not be done.

[snip errors]

testdb=> select lpad(i_pd::char,7,'0') from t_table limit 1;
ERROR:  Cannot cast type 'numeric' to 'character'

[/snip error]

Is it that I *can not* cast numeric into character value or
or do I need some new methodology of doing this?

Thanks!

-X

Re: cast numeric to char

From
Darren Ferguson
Date:
select number::float::text::char FROM test;

You can cast it a few times as shown above to get there

HTH

On Mon, 28 Oct 2002, Johnson, Shaunn wrote:

> Howdy:
>
> Running PostgreSQL 7.2.1 -
>
> I'm trying to cast a numeric field to character, but I
> am getting errors saying that it can not be done.
>
> [snip errors]
>
> testdb=> select lpad(i_pd::char,7,'0') from t_table limit 1;
> ERROR:  Cannot cast type 'numeric' to 'character'
>
> [/snip error]
>
> Is it that I *can not* cast numeric into character value or
> or do I need some new methodology of doing this?
>
> Thanks!
>
> -X
>

--
Darren Ferguson

Re: cast numeric to char

From
"Johnson, Shaunn"
Date:

--thanks for the reply

--but, at the same time, i seem to lose my decimal values.
--should that happen?  how can i keep those values at the
--end?

-X

-----Original Message-----
From: Ian Harding [mailto:ianh@tpchd.org]

Yes, you cannot cast a numeric to character, but there is a function called to_char() that will help.

Something like:

planning=# select to_char(4, '0000009');
 to_char
----------
  0000004

The only annoying thing is that it seems to put a blank in front of the string, I am not sure why.  You will probably want to trim() the results.

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
iharding@tpchd.org

<This Space Intentionally Left Blank>

Howdy:

Running PostgreSQL 7.2.1 -

I'm trying to cast a numeric field to character, but I
am getting errors saying that it can not be done.

[snip errors]

testdb=> select lpad(i_pd::char,7,'0') from t_table limit 1;
ERROR:  Cannot cast type 'numeric' to 'character'

[/snip error]

Is it that I *can not* cast numeric into character value or
or do I need some new methodology of doing this?

Thanks!

-X

Re: cast numeric to char

From
Karel Zak
Date:
On Mon, Oct 28, 2002 at 10:13:02AM -0500, Johnson, Shaunn wrote:
> Yes, you cannot cast a numeric to character, but there is a function called
> to_char() that will help.
>
> Something like:
>
> planning=# select to_char(4, '0000009');
>  to_char
> ----------
>   0000004
>
> The only annoying thing is that it seems to put a blank in front of the
> string, I am not sure why.  You will probably want to trim() the results.

test=# select to_char(4, '99999999');
  to_char
-----------
         4

test=# select to_char(4, 'FM99999999');
 to_char
---------
 4

 See docs, it's better then trim() :-)

    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz