Message-ID: <93C04F1F5173D211A27900105AA8FCFC14542C@lambic.prevuenet.com>
From: Michael J Davis <michael.j.davis@tvguide.com>
To: =?iso-8859-1?Q?=27Jos=E9_Soares=27?= <jose@sferacarta.com>
Cc: pgsql-sql@hub.org
Subject: RE: [SQL] substring
Date: Tue, 27 Apr 1999 09:06:43 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

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é