regexp_substr help, please

#21

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

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),
'^'

Quote:

| '(' -- 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

--
Peter

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

regexp_substr help, please

comp.databases.oracle.misc comp.databases.oracle.misc

Re: regexp_substr help, please - 04-29-2008 , 10:17 PM