From: Michael J Davis <michael.j.davis@tvguide.com>
To: 'José Soares' <jose@sferacarta.com>
Cc: pgsql-sql@hub.org
Subject: RE: [SQL] substring
Date: Tue, 27 Apr 1999 09:06:43 -0500
Message-ID: <93C04F1F5173D211A27900105AA8FCFC14542C@lambic.prevuenet.com> (raw)
I wonder if position is a 6.5 feature? I am using 6.5.
-----Original Message-----
From: José Soares [SMTP:jose@sferacarta.com]
Sent: Tuesday, April 27, 1999 7:02 AM
To: Nuchanard Chiannilkulchai
Cc: pgsql-sql@hub.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@valigene.com]
> > Sent: Monday, April 26, 1999 8:57 AM
> > To: pgsql-sql@hub.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é
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael.j.davis@tvguide.com, jose@sferacarta.com, pgsql-sql@hub.org
Subject: RE: [SQL] substring
In-Reply-To: <93C04F1F5173D211A27900105AA8FCFC14542C@lambic.prevuenet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox