Обсуждение: Trunc in Postgres
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
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
>
>
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
>
>
> 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
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);
}
>
> thnx,
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
"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
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