Thread: last word in text

Is there a function or composition of functions that will retrieve the last
word in a text field? I have a table of addresses with arbitrary text
for the first field (i.e. to whom it is addressed), and I'd like to do my
queries as something like this:

SELECT * from Addresses
WHERE state = 'CA'
ORDER BY lastword(addressee);

What I'm missing is the definition/name of the lastword function.

Note that I'm not expecting perfect results from this. There will be some
weirdness with certain addressees. Nonetheless, it is a reasonable
approach. My other option, which is what I'm doing now, is to have a "last"
column in the table and have my host language fill it on insertion. It's
workable, but it's redundant and overly complex from a data abstraction
point of view.

--Greg

create function lastword(varchar) returns varchar as '
    return [string range $1 [string last " " $1] end]
' language 'pltcl';

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

>>> Gregory Seidman <gss+pg@cs.brown.edu> 01/15/03 07:16AM >>>
Is there a function or composition of functions that will retrieve the last
word in a text field? I have a table of addresses with arbitrary text
for the first field (i.e. to whom it is addressed), and I'd like to do my
queries as something like this:

SELECT * from Addresses
WHERE state = 'CA'
ORDER BY lastword(addressee);

What I'm missing is the definition/name of the lastword function.

Note that I'm not expecting perfect results from this. There will be some
weirdness with certain addressees. Nonetheless, it is a reasonable
approach. My other option, which is what I'm doing now, is to have a "last"
column in the table and have my host language fill it on insertion. It's
workable, but it's redundant and overly complex from a data abstraction
point of view.

--Greg


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Ian Harding sez:
} create function lastword(varchar) returns varchar as '
}     return [string range $1 [string last " " $1] end]
} ' language 'pltcl';

I don't seem to have pltcl available. In fact, all I have available is
plpgsql and sql. Can it be done in either of those languages?

} Ian Harding
--Greg

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.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

>>> Gregory Seidman <gss+pg@cs.brown.edu> 01/15/03 08:13AM >>>
Ian Harding sez:
} create function lastword(varchar) returns varchar as '
}     return [string range $1 [string last " " $1] end]
} ' language 'pltcl';

I don't seem to have pltcl available. In fact, all I have available is
plpgsql and sql. Can it be done in either of those languages?

} Ian Harding
--Greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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
%

} Ian Harding
--Greg

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