Thread: RE: [SQL] substring
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é
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é
>