Postgres Pro
Downloads
Home   >   mailing lists

Thread: RE: [SQL] substring

RE: [SQL] substring

From
Michael J Davis
Date:
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) ?
> 
> I have  in table table_a
> a
> ----------------
> 98-004_c136
> P124_154
> 98-005_c171
> 
> and i want to select only the text before the underscore:
> 
> 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
> 
>     Thanks a lot,
>     Nuch
> 
> 
>

Re: [SQL] substring

From
Nuchanard Chiannilkulchai
Date:
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
> >

Re: [SQL] substring

From
José Soares
Date:
  <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> 

Re: [SQL] substring

From
Tom Lane
Date:
Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
> The problem is : select  position ('_' in a ) from table_a  does not
> work.
>  ERROR:  No such function 'strpos' with the specified attributes

Hmm, I don't see that under either 6.4.2 or current sources.

What data type is your column a?  I tried text and char(n) and neither
one gave an error...
        regards, tom lane

Re: [SQL] substring

From
Nuchanard Chiannilkulchai
Date:
Tom Lane wrote:

> Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
> > The problem is : select  position ('_' in a ) from table_a  does not
> > work.
> >  ERROR:  No such function 'strpos' with the specified attributes
>
> Hmm, I don't see that under either 6.4.2 or current sources.
>
> What data type is your column a?  I tried text and char(n) and neither
> one gave an error...
>
>                         regards, tom lane
   the datatype of a is char(8).
   select a, substring(a from 1 for position('_' in a) - 1) as part from
test;   This solution from José works great, THANX.
   Nuch