Postgres Pro
Downloads
Home   >   mailing lists

Thread: reverse strpos?

reverse strpos?

From
"Gauthier, Dave"
Date:

Is there a function that’ll return the position of the last occurance of a char in a string? 

 

For Example, in the string ‘abc/def/ghi’ I want the position of the 2nd ‘/’.

 

Thanks in Advance.

 

 

 

 

 

Re: reverse strpos?

From
"A. Kretschmer"
Date:
am  Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes:
> Is there a function that?ll return the position of the last occurance of a char
> in a string?
>
>
>
> For Example, in the string ?abc/def/ghi? I want the position of the 2^nd ?/?.

write a function to revert the string and use strpos().

create or replace function rev(varchar) returns varchar as $$
declare
        _temp varchar;
        _count int;
begin
        _temp := '';
        for _count in reverse length($1)..1 loop
                _temp := _temp || substring($1 from _count for 1);
        end loop;
        return _temp;
end;
$$ language plpgsql immutable;


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: reverse strpos?

From
David Fetter
Date:
On Mon, Nov 12, 2007 at 05:19:25PM +0100, A. Kretschmer wrote:
> am  Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes:
> > Is there a function that?ll return the position of the last
> > occurance of a char in a string?
> >
> > For Example, in the string ?abc/def/ghi? I want the position of
> > the 2^nd ?/?.
>
> write a function to revert the string and use strpos().
>
> create or replace function rev(varchar) returns varchar as $$
> declare
>         _temp varchar;
>         _count int;
> begin
>         _temp := '';
>         for _count in reverse length($1)..1 loop
>                 _temp := _temp || substring($1 from _count for 1);
>         end loop;
>         return _temp;
> end;
> $$ language plpgsql immutable;
>
>
> Andreas

PL/Perl might be easier:

CREATE OR REPLACE FUNCTION rev(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE plperl
AS $$
return reverse($_[0]);
$$;

You could also write wrappers around perl functions if you're taking
that route.

If you want to guarantee the thing runs on any modern Postgres
instance--one where you don't control the environment at all--you
could do:

CREATE OR REPLACE FUNCTION rev(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT array_to_string(
    ARRAY(
        SELECT substr($1,i,1)
        FROM generate_series(length($1),1,-1) AS i
    ),
    ''
);
$$;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: reverse strpos?

From
"Gauthier, Dave"
Date:
Great suggestions (I have just GOT to get the IS people around here to
install plperl).

Leveraging what Andreas sent, I created my own strrpos....

create or replace function strrpos(varchar,varchar) returns int as $$
declare
 _count int;
begin

 for _count in reverse length($1)..1 loop

   if(substring($1 from _count for 1) = $2) then
     return _count;
   end if;

 end loop;
 return 0;

end;
$$ language plpgsql immutable;




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Fetter
Sent: Monday, November 12, 2007 11:48 AM
To: A. Kretschmer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] reverse strpos?

On Mon, Nov 12, 2007 at 05:19:25PM +0100, A. Kretschmer wrote:
> am  Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave
folgendes:
> > Is there a function that?ll return the position of the last
> > occurance of a char in a string?
> >
> > For Example, in the string ?abc/def/ghi? I want the position of
> > the 2^nd ?/?.
>
> write a function to revert the string and use strpos().
>
> create or replace function rev(varchar) returns varchar as $$
> declare
>         _temp varchar;
>         _count int;
> begin
>         _temp := '';
>         for _count in reverse length($1)..1 loop
>                 _temp := _temp || substring($1 from _count for 1);
>         end loop;
>         return _temp;
> end;
> $$ language plpgsql immutable;
>
>
> Andreas

PL/Perl might be easier:

CREATE OR REPLACE FUNCTION rev(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE plperl
AS $$
return reverse($_[0]);
$$;

You could also write wrappers around perl functions if you're taking
that route.

If you want to guarantee the thing runs on any modern Postgres
instance--one where you don't control the environment at all--you
could do:

CREATE OR REPLACE FUNCTION rev(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT array_to_string(
    ARRAY(
        SELECT substr($1,i,1)
        FROM generate_series(length($1),1,-1) AS i
    ),
    ''
);
$$;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

Re: reverse strpos?

From
"A. Kretschmer"
Date:
am  Mon, dem 12.11.2007, um  8:48:29 -0800 mailte David Fetter folgendes:
> > > Is there a function that?ll return the position of the last
> > > occurance of a char in a string?
> > >
> >
> > write a function to revert the string and use strpos().
> >
> > create or replace function rev(varchar) returns varchar as $$
> > declare
> > ...
> > $$ language plpgsql immutable;
> >
> >
> > Andreas
>
> PL/Perl might be easier:
>
> CREATE OR REPLACE FUNCTION rev(TEXT)
> RETURNS TEXT
> IMMUTABLE
> LANGUAGE plperl
> AS $$
> return reverse($_[0]);
> $$;

heh, the PERL-Guru ;-)


>
> CREATE OR REPLACE FUNCTION rev(TEXT)
> RETURNS TEXT
> IMMUTABLE
> LANGUAGE SQL
> AS $$
> SELECT array_to_string(
>     ARRAY(
>         SELECT substr($1,i,1)
>         FROM generate_series(length($1),1,-1) AS i
>     ),
>     ''
> );
> $$;

Nice.

The generate_series()-function are really great.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: reverse strpos?

From
"Pavel Stehule"
Date:
On 12/11/2007, Gauthier, Dave <dave.gauthier@intel.com> wrote:
>
>
>
>
> Is there a function that'll return the position of the last occurance of a
> char in a string?
>
>

Hello

simply install and use orafce


http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29

Regards
Pavel Stehule

>
> For Example, in the string 'abc/def/ghi' I want the position of the 2nd '/'.
>
>
>
> Thanks in Advance.
>
>
>
>
>
>
>
>
>
>

Re: reverse strpos?

From
hubert depesz lubaczewski
Date:
On Mon, Nov 12, 2007 at 10:54:53AM -0500, Gauthier, Dave wrote:
> Is there a function that'll return the position of the last occurance of
> a char in a string?
> For Example, in the string 'abc/def/ghi' I want the position of the 2nd
> '/'.

# select length(substring('abc/def/ghi' from '^(.*/)'));
 length
--------
      8
(1 row)

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)