Parse Full Name Field Oracle

Question

I was wondering if anyone could help me with parsing a full name field. I would like to separate it into lastname, firstname, middle initial, suffix.

Here are some inputs for name followed by how I would like for them to be parsed.

                           Parsed Stuff Begins Here-------------------------------------
    name                  | lastname  | firstname        |  middle initial   | suffix |
----------------------------------------------------------------------------------------
PUBLIC, JOHN              | PUBLIC    | JOHN             |  NULL             | NULL
PUBLIC, CHUN CH KIM       | PUBLIC    | CHUN CH KIM      |  NULL             | NULL
PUBLIC, MARY L            | PUBLIC    | MARY             |  L                | NULL
PUBLIC, FRED J JR         | PUBLIC    | FRED             |  J                | JR
PUBLIC, SUE ELLEN J SR    | PUBLIC    | SUE ELLEN        |  J                | SR

I have a list of all the suffix values that one is able to enter, i.e.

JR, SR, I,II,III,IV,V,VI

I've gotten to a point where I split up the lastname and the rest of the name, but I can't quite figure out how to do the rest. I'm using oracle 10g.

This is not a homework question. It's an actual problem I'm working on at work.

Here's what I currently have:

 select id,
        name,
        substr(name,1, instr(name,',')-1) as lname,
        substr(name,(instr(name,',')+1),length(name)) as rest_of_the_name
 from    my_table
 where status='A';

it might help if you show us what you have, not just the problem statement. i repeat the 'is this homework' question. — akf, Jun 16 '09 at 20:13

score 2 · Answer 1 · answered Jun 16 '09 at 20:25

2

This is a problem for which there will always be data that breaks it.

What if there are 2 initials? What if the initials are first, as J Edgar Hoover?

You mention values "that one is able to enter." Can you change the way the values are entered to capture them already separated?

answered Jun 16 '09 at 20:25

UncleO

8,299
21
29

yeah we are planning to change the way they are entered so that they are all captured in different field, but there are around 5 million names that currently exist which need to be parsed. – zSynopsis Jun 16 '09 at 20:28
+1 for the best suggestion - to try to capture the values separated in the first place. – Jeffrey Kemp Jun 17 '09 at 02:19

score 1 · Accepted Answer · answered Jun 17 '09 at 02:16

You've partially solved it already - you can use your query as a subquery and break the problem down bit by bit, e.g.:

select id, name, lname,
       case
       when substr(x, -2, 1) = ' '
       then substr(x, length(x) - 2)
       else x
       end as first_name, -- e.g. "SUE ELLEN"
       case
       when substr(x, -2, 1) = ' ' 
       then substr(x, -1)
       else null
       end as middle_initial, -- e.g. "J"
       suffix -- e.g. "SR"
from (
select id, name, lname, suffix,
       case when suffix is not null then
       substr(rest_of_the_name, 1, length(rest_of_the_name)-length(suffix)-1)
       else rest_of_the_name end
       as x -- e.g. "SUE ELLEN J"
from (
select id, name, lname, rest_of_the_name,
       case
       when substr(rest_of_the_name,-2)
            in (' I',' V')
       then substr(rest_of_the_name,-1)
       when substr(rest_of_the_name,-3)
            in (' JR',' SR',' II',' IV',' VI')
       then substr(rest_of_the_name,-2)
       when substr(rest_of_the_name,-4)
            in (' III')
       then substr(rest_of_the_name,-3)
       else null
       end as suffix -- e.g. "SR"
from (
select id,
       name, --e.g. "PUBLIC, SUE ELLEN J SR"
       trim(substr(name,1, instr(name,',')-1)) as lname, -- e.g. "PUBLIC"
       trim(substr(name,(instr(name,',')+1),length(name)))
          as rest_of_the_name -- e.g. "SUE ELLEN J SR"
from    my_table
where status='A'
)));

Thanks Jeffery! I've altered the query just a bit and i ended up with a solution that works for most of the names in our db. — zSynopsis, Jun 17 '09 at 14:04
You may find you'll have to keep changing it quite a bit depending on the consistency of the data - to the point where it will be easier to use a procedural solution. — Jeffrey Kemp, Jun 18 '09 at 01:58
Jeffrey Kemp mentioned the OP might need a procedural solution. Is there any chance someone's already created one? — StewS2, Jan 17 '20 at 19:08

score 0 · Answer 3 · answered Jan 08 '10 at 19:46

Here's an unsophisticated answer, based on the retrieving the first and last name, retrieving the MI the same way as the first name, removing the MI from 'rest_of_the_name' as the last name.

SELECT
substr('John Q. Public',1, instr('John Q. Public',' ')-1) as FirstName,
substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')) as rest_of_the_name,
substr(substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')),1, instr(substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')),' ')-1) as MI,
replace(substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')), substr(substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')),1, instr(substr('John Q. Public',(instr('John Q. Public',' ')+1),length('John Q. Public')),' ')-1)) as LastName
FROM DUAL;

score 0 · Answer 4 · answered Jan 17 '20 at 19:12

This post contained a pretty complete SQL-only solution, written in SQLServer syntax. I've converted it to Oracle syntax. It does a pretty good job, though it doesn't handle suffixes (Jr., III, etc). You should read the other caveats in the post:

SQL: parse the first, middle and last name from a fullname field

SELECT first_name.original_input_data,
       first_name.title,
       first_name.first_name,
       CASE
           WHEN 0 = INSTR(first_name.rest_of_name, ' ') THEN
            NULL --no more spaces?  assume rest is the last name
           ELSE
            SUBSTR(first_name.rest_of_name, 1, INSTR(first_name.rest_of_name, ' ') - 1)
       END AS middle_name,
       SUBSTR(first_name.rest_of_name,
              1 + INSTR(first_name.rest_of_name, ' '),
              LENGTH(first_name.rest_of_name)) AS last_name
  FROM (SELECT title.title,
                CASE
                    WHEN 0 = INSTR(title.rest_of_name, ' ') THEN
                     title.rest_of_name --No space? return the whole thing
                    ELSE
                     SUBSTR(title.rest_of_name, 1, INSTR(title.rest_of_name, ' ') - 1)
                END AS first_name,
                CASE
                    WHEN 0 = INSTR(title.rest_of_name, ' ') THEN
                     NULL --no spaces @ all?  then 1st name is all we have
                    ELSE
                     SUBSTR(title.rest_of_name, INSTR(title.rest_of_name, ' ') + 1, LENGTH(title.rest_of_name))
                END AS rest_of_name,
                title.original_input_data
           FROM (SELECT
                  --if the first three characters are in this list,
                  --then pull it as a "title".  otherwise return NULL for title.
                   CASE
                       WHEN SUBSTR(test_data.full_name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN
                        LTRIM(RTRIM(SUBSTR(test_data.full_name, 1, 3)))
                       ELSE
                        NULL
                   END AS title
                   --if you change the list, don't forget to change it here, too.
                 --so much for the DRY prinicple...
                ,
                 CASE
                     WHEN SUBSTR(test_data.full_name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN
                      LTRIM(RTRIM(SUBSTR(test_data.full_name, 4, LENGTH(test_data.full_name))))
                     ELSE
                      LTRIM(RTRIM(test_data.full_name))
                 END AS rest_of_name,
                 test_data.original_input_data
                  FROM (SELECT
                        --trim leading & trailing spaces before trying to process
                        --disallow extra spaces *within* the name
                         REPLACE(REPLACE(LTRIM(RTRIM(full_name)), '  ', ' '), '  ', ' ') AS full_name,
                         full_name AS original_input_data
                          FROM ( --if you use this, then replace the following
                                --block with your actual table
                                SELECT 'george w bush jr.' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'SUSAN B ANTHONY' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'alexander hamilton' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'osama bin laden jr' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'Martin J. VAN BUREN SENIOR III' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'Tommy' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'Billy' AS full_name
                                  FROM dual
                                UNION
                                SELECT NULL AS full_name
                                  FROM dual
                                UNION
                                SELECT ' ' AS full_name
                                  FROM dual
                                UNION
                                SELECT '    JOHN  JACOB     SMITH' AS full_name
                                  FROM dual
                                UNION
                                SELECT ' DR  SANJAY       GUPTA' AS full_name
                                  FROM dual
                                UNION
                                SELECT 'DR JOHN S HOPKINS' AS full_name
                                  FROM dual
                                UNION
                                SELECT ' MRS  SUSAN ADAMS' AS full_name
                                  FROM dual
                                UNION
                                SELECT ' MS AUGUSTA  ADA   SMITH-KING ' AS full_name
                                  FROM dual) raw_data) test_data) title) first_name

score -2 · Answer 5 · answered Dec 03 '13 at 04:39

-2

Select SUBSTR(name, INSTR(name, ' ')+1) AS lastname, SUBSTR(name, 1, INSTR(name, ' ')-1) AS firstname from whatever;

answered Dec 03 '13 at 04:39

Pete

1

Parse Full Name Field Oracle

5 Answers5