Trunc in Postgres

You can try

select CAST(cast('0000000000000000000000004' as NUMERIC(20)) AS varchar);

Pavel

Your solution works in this example, but when
I take a columnname which type is char(30)
I beome the following error:

ERROR: Cannot cast type character to integer

-----Ursprüngliche Nachricht-----
Von: Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz]
Gesendet: Mittwoch, 3. September 2003 15:56
An: Amin Schoeib
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Trunc in Postgres

You can try

select CAST(cast('0000000000000000000000004' as NUMERIC(20)) AS varchar);

Pavel

You can try
select CAST(cast('0000000000000000000000004' as NUMERIC(20)) AS varchar);

noobie question:

Do multiple casts like that slow down the query (in general and not specifically
for the query above)? If not, what about if the query is complex?

thnx,
Chris

"Amin Schoeib" <aschoeib@4tek.de> writes:

Is there an equivalent for the trunc function of Oracle in Postgres???
I need to trunc(the zeros) a number which is stored as a char with a lot of=
zeros
Like that : 0000000000000000000000000000004
In oracle you can make that by trunc(YOUR_COLUMNNAME,0)

We use the SQL-standard spelling, TRIM().

regards, tom lane

Look at the "trim" function.
While you are about it, looking over the other
available functions would be worth your while too
(look under "Functions and Operators" in the docs).

--- Amin Schoeib <aschoeib@4tek.de> wrote:

Hi,
Is there an equivalent for the trunc function of
Oracle in Postgres???
I need to trunc(the zeros) a number which is stored
as a char with a lot of zeros
Like that : 0000000000000000000000000000004

In oracle you can make that by
trunc(YOUR_COLUMNNAME,0)

Thanxx

Schoeib

4Tek Gesellschaft f���r angewandte
Informationstechnologien mbH
Schoeib Amin
Tel. +49 (0) 69 697688-132
Fax. +49 (0) 69 697688-111
http://www.4tek.de

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

On Wed, 3 Sep 2003, Chris Boget wrote:

You can try
select CAST(cast('0000000000000000000000004' as NUMERIC(20)) AS varchar);

noobie question:

Do multiple casts like that slow down the query (in general and not specifically
for the query above)? If not, what about if the query is complex?

I don't know. This is simple solution. You can write UDF in c if qwery
will be slowly, like this?

PG_FUNCTION_INFO_V1 (ztrim);
Datum ztrim(PG_FUNCTION_ARGS)
{
BpChar *rc = PG_GETARG_BPCHAR_P(0);
int l = VARSIZE (rc) - VARHDRSZ;
while (*rc == ' ' || *rc == '0')
{
rc++;
if (--l == 0) break;
}
BpChar *res;
res = palloc (VARHDRSZ + 1);
VARATT_SIZEP (res) = VARHDRSZ + 1;
strncpy(VARDATA(res), rc, l);
PG_RETURN_BPCHAR_P (res);
}

Try:
select '0000000000000000000000041'::integer;

Just turn it into a number if you want to truncate the
leading zeros. But if you want text output,
trim() trim is it.

Trim() trims from text fields.
select trim( '0' from '00000000000000000000000041');

Also see the replace() function.

--elein