Re: Parsing Full Names

You probably and to get very "creative" with substr and instr, but it can be VERY messy, but it'll work. For best results, make sure that the full name in entered consistently (like with a space between each part, or a comma between last and first, but not mixed across rows). Here's an example where I did this with the comma between the first and last names (some records had middle initials and some didn't, so I handled each case separately):

(The long lines probably might display funny - sorry)

• Split NAME into FNAME, INIT, and LNAME...
• This one is for names with initials... update tcust set lname = substr(substr(name, 1, (instr(name,',')-1)),1,20), init = substr(name,instr(name,'.',-1)-1,1), name = null fname = substr(substr(name,(decode(instr(name,','),0,(decode(instr(name,',') ,0,decode(instr(name,' '),0,length(name)+1,instr(name,' ')+1), instr(name,',')+1 )),instr(name,', ')+2)),(decode((instr(name,' ', 1,3)),0,decode((instr(name,' ', 1,2)),0,length(name)+1,(instr(name,' ',1,2))),(instr(name,' ',1,3)))-decode(inst r(name,', '),0,(decode(instr(name,','),0,decode(instr(name,' '), 0,length(name)+ 1,instr(name,' ')+1),instr(name,',')+1)),instr(name,', ')+1))),1,15), where name is not null and instr(name,'.') != 0 / commit /
• This one is for names with no initials... update tcust set lname = substr(substr(name, 1, (instr(name,',')-1)),1,20), fname = substr(ltrim(substr(name, instr(name,',')+1, length(name)-instr(name ,',')),' '),1,15), name = null where name is not null and instr(name,'.') = 0 / commit /

--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)

mike_andrew_at_usiva.com wrote in article <6m8oea$j5g$1_at_nnrp1.dejanews.com>...
> Any ideas on parsing full names(within the database) into seperate
fields?
>
> E.G.
>
> Given in table:
> EMP_NAME
> -------------------------------
> SMITH JOHN B
>
> Desired result in table: EMP_NAME EMP_LNAME EMP_FNAME EMP_MI
> ------------------------------- --------------------------
> --------------------------- ------------------ SMITH JOHN B SMITH JOHN
B
>
> Thanks in advance!
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
Received on Wed Jun 17 1998 - 15:24:20 CDT