RE: [SQL] substring

I wonder if position is a 6.5 feature? I am using 6.5.

-----Original Message-----
From: José Soares [SMTP:jose(at)sferacarta(dot)com]
Sent: Tuesday, April 27, 1999 7:02 AM
To: Nuchanard Chiannilkulchai
Cc: pgsql-sql(at)hub(dot)org
Subject: Re: [SQL] substring

Nuchanard Chiannilkulchai ha scritto:

The problem is : select  position ('_' in a ) from table_a 
does not
work.
 ERROR:  No such function 'strpos' with the specified
attributes

while  select position('_' in '98-004_c136') ;    give the
right answer.

 I wonder how to introduce the attribute , and not the
constant value.

Michael J Davis wrote:

> try:
>
> select a, substring(a, 1, position('_' in a) -1) from
table_a;
>
> I have not tested this.  Not sure if the parameters to
substring are
> correct.  Also not sure if the -1 is needed.
>
> > -----Original Message-----
> > From: Nuchanard Chiannilkulchai [SMTP:nuch(at)valigene(dot)com]

> > Sent: Monday, April 26, 1999 8:57 AM
> > To:   pgsql-sql(at)hub(dot)org
> > Subject:      [SQL] substring
> >
> > Hello,
> >
> >     How should I do my query to put a substring value in
a field, in
> > postgres ( I have 6.4) ?
> >   [snips]

> > in sybase, this should be
> >  select a, substring(a,1,charindex('_',a)-1)  from
table_a
> >  a
> >  ---------------- ----------------
> >  98-004_c136      98-004
> >  98-005_c171      98-005
> >  P124_154         P124
> >

charindex() is not SQL standard, with PostgreSQL you can
use this portable SQL standard query:

select a, substring(a from 1 for position('_' in a) - 1) as part
from test;
a          |part
-----------+------
98-004_c136|98-004
98-005_c171|98-005
P124_154   |P124
(3 rows)

José