Re: [GENERAL] Trunc in Postgres

[Tom Lane]

"Amin Schoeib" <[EMAIL PROTECTED]> 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 : 004
> In oracle you can make that by trunc(YOUR_COLUMNNAME,0)

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

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Trunc in Postgres

[Pavel Stehule]

On Wed, 3 Sep 2003, Chris Boget wrote:

> > You can try
> > select CAST(cast('4' 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);
}

> 
> thnx,
> Chris
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Trunc in Postgres

[Jeff Eckermann]

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 <[EMAIL PROTECTED]> 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 : 004
> 
> 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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Trunc in Postgres

[Amin Schoeib]

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:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 3. September 2003 15:56
An: Amin Schoeib
Cc: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] Trunc in Postgres


You can try

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

Pavel
> 
> 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 : 004
> 
> 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
> 
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])