Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trunc in Postgres

Trunc in Postgres

From
"Amin Schoeib"
Date:

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

Re: Trunc in Postgres

From
Pavel Stehule
Date:
You can try

select CAST(cast('0000000000000000000000004' 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 : 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
>
>

Re: Trunc in Postgres

From
"Amin Schoeib"
Date:
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
>
> 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
>
>

Re: Trunc in Postgres

From
"Chris Boget"
Date:
> 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

Re: Trunc in Postgres

From
Jeff Eckermann
Date:
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

Re: Trunc in Postgres

From
Pavel Stehule
Date:
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);
}

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

Re: Trunc in Postgres

From
Tom Lane
Date:
"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

Re: Trunc in Postgres

From
elein
Date:
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

On Wed, Sep 03, 2003 at 07:44:34AM -0700, Jeff Eckermann wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings