Re: regexp_substr help, please
From: Peter Nilsson <airia_at_acay.com.au>
Date: Tue, 29 Apr 2008 20:17:57 -0700 (PDT)
Message-ID: <dcf2ea9b-04af-4f02-b954-0fec7e05d474@t12g2000prg.googlegroups.com>
)
select
nme,
regexp_replace
(
trim(nme),
'"\2\5", "\3\8"', -- "surname", "given" 1, 0, 'i' -- case insensitive
)
from
names
Date: Tue, 29 Apr 2008 20:17:57 -0700 (PDT)
Message-ID: <dcf2ea9b-04af-4f02-b954-0fec7e05d474@t12g2000prg.googlegroups.com>
Doug Miller 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
Start with a 'student' aproach...
with
names as
(
select 'mc winter, first' nme from dual union all select 'mc. winter, first' nme from dual union all select 'winter, first second' nme from dual union all select 'mc winter,first' nme from dual union all select 'mc. winter,first' nme from dual union all select 'winter,first' nme from dual union all select 'mc winter first second' nme from dual union all select 'mc. winter first' nme from dual union all select 'winter first' nme from dual union all select 'macwilliams' nme from dual
)
select
nme,
regexp_replace
(
trim(nme),
'^'
|| '(' -- with comma || '([^,]*)' -- surname || ', *' || '(.*)' -- given name(s) || ')' || '|' || '(' -- without comma || '(' -- surname || '((mc|st)\.?)? *' -- optional prefix || '[^ ]+' || ')' || ' *' || '(.*)' -- given name(s) || ')' || '$',
'"\2\5", "\3\8"', -- "surname", "given" 1, 0, 'i' -- case insensitive
)
from
names
-- PeterReceived on Tue Apr 29 2008 - 22:17:57 CDT