Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
Hello,
I need help splitting a Full Name into a First Name, Middle (if it exists), Last Name, and Suffix (if it exists).
I'm close, but I'm having trouble getting all the possible combinations to work out correctly.
This is what I have so far:
SELECT SUBSTR(full_name,0,INSTR(full_name,' ')- 1) AS first_name
,SUBSTR(full_name,INSTR(full_name,' ') +1, INSTR(full_name,' ',-1,1) - INSTR(full_name,' ') -1) AS mid_name
,SUBSTR(full_name,INSTR(full_name,' ',-1)+ 1) AS last_name
,SUBSTR(full_name,INSTR(full_name,',',-1)+2) AS suffix
FROM names;
Here's the create statements:
CREATE TABLE names
(
full_name VARCHAR2 (25)
);
INSERT INTO names (full_name)
VALUES ('Ronald V. McDonald, DO');
INSERT INTO names (full_name)
VALUES ('Fred Derf, DD');
INSERT INTO names (full_name)
VALUES ('Pig Pen');
Can someone help me with this?
Thanks,
Joe