Thread: RE: [SQL] substring

RE: [SQL] substring

From
Michael J Davis
Date:
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é  


Re: [SQL] substring

From
José Soares
Date:

Michael J Davis ha scritto:

> I wonder if position is a 6.5 feature?  I am using 6.5.

POSITION function was implemented on v6.2.
José

>
>
>         -----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é
>