Re: 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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