regexp_substr help, please

#1

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?

#2

On Apr 29, 10:34 am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

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?

These are you combos?

ln,fn|ln, fn|ln fn
l n,fn|l n, fn|l n fn

I'd start by nuking the fn. It's the last solid string and it has an
nice anchor fn$

#3

On Apr 29, 10:34 am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

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?

These are you combos?

ln,fn|ln, fn|ln fn
l n,fn|l n, fn|l n fn

I'd start by nuking the fn. It's the last solid string and it has an
nice anchor fn$

#4

On Apr 29, 10:34 am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

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?

These are you combos?

ln,fn|ln, fn|ln fn
l n,fn|l n, fn|l n fn

I'd start by nuking the fn. It's the last solid string and it has an
nice anchor fn$

#5

On Apr 29, 10:34 am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

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?

These are you combos?

ln,fn|ln, fn|ln fn
l n,fn|l n, fn|l n fn

I'd start by nuking the fn. It's the last solid string and it has an
nice anchor fn$

#6

On Apr 29, 12:43 pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

Doug Miller (spamb... (AT) milmac (DOT) com) wrote:

: 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

[A-Z] doesn't match o

Here a perl test I did to check out a maybe.
@ is an array of your combos (did I get them all?)
The loop goes thru each. $1 will contain the "last name".

@s = ("mc winter, first",
"mc. winter, first",
"winter, first",
"mc winter,first",
"mc. winter,first",
"winter,first",
"mc winter first",
"mc. winter first",
"winter first",
);

foreach $x (@s) {
print qq/look at "$x"\n/;
{
$x =~ /(.*)?[, ]{1}[a-zA-Z]*$/;
print $1 . "\n";
}
}

#7

On Apr 29, 12:43 pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

Doug Miller (spamb... (AT) milmac (DOT) com) wrote:

: 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

[A-Z] doesn't match o

Here a perl test I did to check out a maybe.
@ is an array of your combos (did I get them all?)
The loop goes thru each. $1 will contain the "last name".

@s = ("mc winter, first",
"mc. winter, first",
"winter, first",
"mc winter,first",
"mc. winter,first",
"winter,first",
"mc winter first",
"mc. winter first",
"winter first",
);

foreach $x (@s) {
print qq/look at "$x"\n/;
{
$x =~ /(.*)?[, ]{1}[a-zA-Z]*$/;
print $1 . "\n";
}
}

#8

On Apr 29, 12:43 pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

Doug Miller (spamb... (AT) milmac (DOT) com) wrote:

: 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

[A-Z] doesn't match o

Here a perl test I did to check out a maybe.
@ is an array of your combos (did I get them all?)
The loop goes thru each. $1 will contain the "last name".

@s = ("mc winter, first",
"mc. winter, first",
"winter, first",
"mc winter,first",
"mc. winter,first",
"winter,first",
"mc winter first",
"mc. winter first",
"winter first",
);

foreach $x (@s) {
print qq/look at "$x"\n/;
{
$x =~ /(.*)?[, ]{1}[a-zA-Z]*$/;
print $1 . "\n";
}
}

#9

On Apr 29, 12:43 pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

Doug Miller (spamb... (AT) milmac (DOT) com) wrote:

: 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

[A-Z] doesn't match o

Here a perl test I did to check out a maybe.
@ is an array of your combos (did I get them all?)
The loop goes thru each. $1 will contain the "last name".

@s = ("mc winter, first",
"mc. winter, first",
"winter, first",
"mc winter,first",
"mc. winter,first",
"winter,first",
"mc winter first",
"mc. winter first",
"winter first",
);

foreach $x (@s) {
print qq/look at "$x"\n/;
{
$x =~ /(.*)?[, ]{1}[a-zA-Z]*$/;
print $1 . "\n";
}
}

#10

In article <4817502b$1 (AT) news (DOT) victoria.tc.ca>, yf110 (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones) wrote:

Quote:

Doug Miller (spambait (AT) milmac (DOT) com) wrote:
: 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

[A-Z] doesn't match o

Yeah, I just got back from lunch and realized the same thing. Knew it had to
be something stupid like that. Thanks.