REGEX NAME Matching..

[michael]

Hello, I am bit new to using REGEX.. so I have data that looks like the
following all in one column called target_name and I need to separate
it out into First Name, Middle Name, Last Name, Title One, Title Two.

Examples
John Doe, MBA
John J Doe
John Doe
John Michael Doe,MFA,MS

I am having the most difficulty with the middle name spelled out vs the
last name.. There must be a way to either match from the first comma
back or the 3rd/2nd space back or the end, something like this --> [,[
]{2|3}$]? but that is not working... or any other suggestion would be
much appreciated.


I have done something like the below

SELECT
REGEXP_SUBSTR(c.TARGET_NAME,'^[A-z-]+') FIRST_NAME,
REGEXP_SUBSTR(c.TARGET_NAME,' [A-z-]+{1}\.? ') MIDDLE_NAME,
REGEXP_REPLACE(c.TARGET_NAME,' ([A-Z][a-z]+)[, $]?','\1')
LAST_NAME,
c.TARGET_NAME

FROM blah.mytable c;

 

[Tad McClellan]

I need to separate
it out into First Name, Middle Name, Last Name, Title One, Title Two.

Examples
John Doe, MBA
John J Doe
John Doe
John Michael Doe,MFA,MS

I am having the most difficulty with the middle name spelled out vs the
last name..

That is only because your test data does not contain the
even more difficult cases. There are no middle names in:

Billy Bob Thorton
Mohamed El Habib

for instance.

Can you get the correct last name and title for:

George Forman VI
Martin Luther King Jr.
Martin Luther King, Jr.

??

or any other suggestion would be
much appreciated.

-------------------------------------
#!/usr/bin/perl
use warnings;
use strict;

$" = '::';
while ( <DATA> ) {
my $comma_pos = index($_, ',');
my @names = substr($_, 0, $comma_pos) =~ /(\S+)/g;
my @titles = substr($_, $comma_pos) =~ /([^,]+)/g;
print "names: @names\n";
print "titles: @titles\n";
}

__DATA__
John Doe, MBA
John J Doe
John Doe
John Michael Doe,MFA,MS
-------------------------------------

I have done something like the below

SELECT
REGEXP_SUBSTR(c.TARGET_NAME,'^[A-z-]+') FIRST_NAME,
REGEXP_SUBSTR(c.TARGET_NAME,' [A-z-]+{1}\.? ') MIDDLE_NAME,
REGEXP_REPLACE(c.TARGET_NAME,' ([A-Z][a-z]+)[, $]?','\1')
LAST_NAME,
c.TARGET_NAME

FROM blah.mytable c;

This is the *Perl* newsgroup.

The SQL newsgroup is over thataway ====>