Re: Request to add feature to the Position function

[Mark Watson <mark.watson@xxxxxxxxxxxxxxx>]

 

 

De : pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] De la part de Ron Ben
Envoyé : Monday, March 27, 2017 11:05 AM
À : pgsql-general@xxxxxxxxxxxxxx
Objet : [GENERAL] Request to add feature to the Position function

<clip>

> position(substring in string)

> as listed here:

> https://www.postgresql.org/docs/9.1/static/functions-string.html

> locates sub string in a string.

> 

> It doesn't support locateing the substring from the back.

<clip>

 

If what you mean by ‘from the back’  is ‘the last occurrence in a string read from left to right’, here is a quickie plpgsql function:

 

CREATE OR REPLACE FUNCTION rposition(substr text, str text)

  RETURNS integer AS

$BODY$

declare

pos integer;

lastpos integer;

begin

pos := position(substr in str);

lastpos := 0;

 

while pos <> 0 loop

            lastpos := pos;

            pos := position(substr in substring(str from pos + 1));

            if pos > 0 then pos := pos + lastpos ; end if;

end loop;

return lastpos;

end;

$BODY$

  LANGUAGE plpgsql IMMUTABLE