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é