You want to use double-quotes, not single quotes:
> SELECT substring("CalledStationId" from 2 for 3) FROM calls WHERE
> h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
> min');
---------------------------------------------------------------------------
Peter Nixon wrote:
-- Start of PGP signed section.
> I guess I don't understand how to use it properly. can anyone give me some
> more help. When I fire off the following query (at a database of radius
> records)
>
>
> SELECT substring('CalledStationId' from 2 for 3) FROM calls WHERE
> h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
> min');
>
> I get:
>
> substring
> -----------
> all
> all
> all
> all
> all
> all
> all
> all
> (8 rows)
>
>
> I if what I want is the behaviour mysql gives with left. ie.
>
> SELECT left(CalledStationId, 3) FROM calls WHERE h323connecttime BETWEEN
> DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1 min');
>
> result
> -----------
> 222
> 222
> 223
> 234
> 444
> 777
> 777
> 778
> (8 rows)
>
>
> ie a list of area codes (which I then want to group on to give a sum per
> area code)
>
> Can someone suggest show me a cluestick here please?
>
> -Peter
>
> On Fri, 25 Oct 2002 12:36:24 -0400 (EDT)
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> >
> > Try substring().
> >
> > ------------------------------------------------------------------------
> > ---
> >
> > Peter Nixon wrote:
> > > Hi Guys
> > >
> > > I am trying to do a query similar to the following:
> > >
> > > select left(field,3) from table;
> > >
> > > Now this works in Mysql/Access etc etc, but not in postgres
> > > (I am running PostgreSQL 7.2)
> > >
> > > Can anyone suggest how to do this in postgres? I have a database with
> > > many millions of records, and this functionality is essential for me.
> > >
> > > PS. I did do a google search etc before posting.
-- End of PGP section, PGP failed!
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
