RE: [SQL] substring - Mailing list pgsql-sql

From Michael J Davis
Subject RE: [SQL] substring
Date
Msg-id 93C04F1F5173D211A27900105AA8FCFC14542C@lambic.prevuenet.com
Whole thread Raw
List pgsql-sql
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 AMTo:
NuchanardChiannilkulchaiCc:    pgsql-sql@hub.orgSubject:    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:
NuchanardChiannilkulchai [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é  


pgsql-sql by date:

Previous
From: Mr M Pacey
Date:
Subject: Arrays and count()
Next
From: Brook Milligan
Date:
Subject: rules help