strposrev

Library Snippets

strposrev(text,text)

Works with PostgreSQL

9.1

Written in

PL/pgSQL

Depends on

Nothing

The string function strposrev(instring text, insubstring text) returns the position of a substring within a string, starting from the end, reverse to the built in strpos(text,text) function. It is like the instrrev() function known from vb, or the string.lastindexof() method known from java and csharp.

CREATE OR REPLACE FUNCTION strposrev(instring text, insubstring text)
  RETURNS integer AS
$BODY$
  DECLARE result INTEGER;
  BEGIN
    IF strpos(instring, insubstring) = 0 THEN
      -- no match
      result:=0;
    ELSEIF length(insubstring)=1 THEN
      -- add one to get the correct position from the left.
      result:= 1 + length(instring) - strpos(reverse(instring), insubstring);
    ELSE 
      -- add two minus the legth of the search string
      result:= 2 + length(instring)- length(insubstring) - strpos(reverse(instring), reverse(insubstring));
    END IF;
    RETURN result;
  END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 4;

see also

• functions-string strpos(text,text)