Re: [SQL] substring

  <p>Nuchanard Chiannilkulchai ha scritto: <blockquote type="CITE">The problem is : select  position ('_' in a ) from
table_a does not <br />work. <br /> ERROR:  No such function 'strpos' with the specified attributes <p>while  select
position('_'in '98-004_c136') ;    give the right answer. <p> I wonder how to introduce the attribute , and not the
constantvalue. <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.  Not sure if the parameters to substring are <br
/>>correct.  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:   pgsql-sql@hub.org <br />> > Subject:      [SQL] substring <br />> > <br />> > Hello,
<br/>> > <br />> >     How should I do my query to put a substring value in a field, in <br />> >
postgres( I have 6.4) ? <br />> >   [snips] <p>> > in sybase, this should be <br />> >  select a,
substring(a,1,charindex('_',a)-1) from table_a <br />> >  a <br />> >  ---------------- ----------------
<br/>> >  98-004_c136      98-004 <br />> >  98-005_c171      98-005 <br />> >  P124_154         P124
<br/>> ></blockquote><tt>charindex() is not SQL standard, with PostgreSQL you can</tt><br /><tt>use this portable
SQLstandard 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          |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   |P124</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>José</tt><br
/><tt></tt>