Re: last word in text

On Wed, 15 Jan 2003, Gregory Seidman wrote:

> Ian Harding sez:
> } I am certain it can, but you could make pltcl available (if you have
> } the rights) by typing
> }
> } createlang 'pltcl' yourdatabasename
> }
> } at the command prompt.
>
> I have the rights, but I don't seem to have the library. I don't think I
> compiled it when I configured/built postgresql in the first place.
>
> % createlang --username=postgres --pglib=/Volumes/Database/postgresql/lib 'pltcl' template0
> ERROR:  stat failed on file '/Volumes/Database/postgresql/lib/pltcl': No such file or directory
> createlang: language installation failed
> %


select substring(rtrim(' ', a.sentence) from '\w+$') from atable a;

Well at least for 7.3.x I can't remember but I presume you're still on an older
production release.


create function last_word ( text ) returns text as '
  declare
    frompos integer;
    ostr alias for $1;
  begin
    while strpos($ostr, ' ') > 0 loop
      frompos := strpos($ostr, ' ') + 1;
      ostr := substring($ostr, $frompos);
    end loop;
    return $ostr;
  end;
' language 'plpgsql';

It ain't pretty, it ain't perfect and it ain't optimised but it might do as a
first draft for you.


--
Nigel J. Andrews