regexp_substr help, please

I need to be able to pull just the last name out of a string consisting of
lastname and firstname, separated by a comma, or space, or comma and space.
Complicating matters somewhat is the fact that lastname might be something
like "Mc Kay" or "St. Louis" so simply grabbing everything before the first
space isn't sufficient.

The closest I've come so far is
select regexp_substr ('St. Louis, Ted', '.{4}[A-Z]+') from dual;
but this returns only
St. L

I thought regular expression matching was supposed to be "greedy", and take as
many characters as would satisfy the pattern ("St. Louis" in this case).

What am I doing wrong?
.

Relevant Pages

  • Re: regexp_substr help, please
    ... I need to be able to pull just the last name out of a string consisting of ... lastname and firstname, separated by a comma, or space, or comma and space. ... be something stupid like that. ...
    (comp.databases.oracle.misc)
  • Re: split "abc,123"
    ... >In the first pane, choose Delimited, then click Next ... >In the second pane, check the Comma checkbox, then click ... >> allows me to pull everything before the comma into one ... >> another cell. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: I want first name only
    ... Try this formula and pull down. ... check both space and other> enter a coma> Finish. ... want to trim off everything before the comma as well as the space after ... Barrett9699's Profile: ...
    (microsoft.public.excel.misc)
  • Re: seperating information
    ... I need to put both numbers into their own seperate field. ... the information before the comma and put it in the customerID field and ... pull the data from after the comma and put it in the vendorID field. ...
    (microsoft.public.access.modulesdaovba)
  • Re: regexp_substr help, please
    ... I need to be able to pull just the last name out of a string consisting of ... lastname and firstname, separated by a comma, or space, or comma and space. ... The loop goes thru each. ...
    (comp.databases.oracle.misc)