Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
	by hub.org (8.9.3/8.9.1) with ESMTP id JAA83415
	for <pgsql-sql@hub.org>; Tue, 27 Apr 1999 09:03:39 -0400 (EDT)
	(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it
 [193.207.10.213])
	by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id PAA11747;
	Tue, 27 Apr 1999 15:03:33 +0200 (MDT)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
	by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
	id 10c9Ma-0007X2-00; Tue, 27 Apr 1999 15:01:32 +0000
Message-ID: <3725B52C.6012C851@sferacarta.com>
Date: Tue, 27 Apr 1999 15:01:32 +0200
From: =?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
X-Mailer: Mozilla 4.5 [it] (Win95; I)
X-Accept-Language: it
MIME-Version: 1.0
To: Nuchanard Chiannilkulchai <nuch@valigene.com>
CC: pgsql-sql@hub.org
Subject: Re: [SQL] substring
References: <93C04F1F5173D211A27900105AA8FCFC14541D@lambic.prevuenet.com>
	<37257226.17A344FA@valigene.com>
Content-Type: multipart/alternative;
	boundary="------------34DECD633F44B5A414C390C0"


--------------34DECD633F44B5A414C390C0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit



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é


--------------34DECD633F44B5A414C390C0
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
&nbsp;
<p>Nuchanard Chiannilkulchai ha scritto:
<blockquote TYPE=CITE>The problem is : select&nbsp; position ('_' in a
) from table_a&nbsp; does not
<br>work.
<br>&nbsp;ERROR:&nbsp; No such function 'strpos' with the specified attributes
<p>while&nbsp; select position('_' in '98-004_c136') ;&nbsp;&nbsp;&nbsp;
give the right answer.
<p>&nbsp;I wonder how to introduce the attribute , and not the constant
value.
<p>Michael J Davis wrote:
<p>> try:
<br>>
<br>> select a, substring(a, 1, position('_' in a) -1) from table_a;
<br>>
<br>> I have not tested this.&nbsp; Not sure if the parameters to substring
are
<br>> correct.&nbsp; Also not sure if the -1 is needed.
<br>>
<br>> > -----Original Message-----
<br>> > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
<br>> > Sent: Monday, April 26, 1999 8:57 AM
<br>> > To:&nbsp;&nbsp; pgsql-sql@hub.org
<br>> > Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [SQL] substring
<br>> >
<br>> > Hello,
<br>> >
<br>> >&nbsp;&nbsp;&nbsp;&nbsp; How should I do my query to put a substring
value in a field, in
<br>> > postgres ( I have 6.4) ?
<br>> >&nbsp;&nbsp; [snips]
<p>> > in sybase, this should be
<br>> >&nbsp; select a, substring(a,1,charindex('_',a)-1)&nbsp; from table_a
<br>> >&nbsp; a
<br>> >&nbsp; ---------------- ----------------
<br>> >&nbsp; 98-004_c136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 98-004
<br>> >&nbsp; 98-005_c171&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 98-005
<br>> >&nbsp; P124_154&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
P124
<br>> ></blockquote>
<tt>charindex() is not SQL standard, with PostgreSQL you can</tt>
<br><tt>use this portable SQL standard query:</tt><tt></tt>
<p><tt>select a, substring(a from 1 for position('_' in a) - 1) as part
from test;</tt>
<br><tt>a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |part</tt>
<br><tt>-----------+------</tt>
<br><tt>98-004_c136|98-004</tt>
<br><tt>98-005_c171|98-005</tt>
<br><tt>P124_154&nbsp;&nbsp; |P124</tt>
<br><tt>(3 rows)</tt><tt></tt>
<p><tt>Jos&eacute;</tt>
<br><tt></tt>&nbsp;</html>

--------------34DECD633F44B5A414C390C0--