Parse String data to pick only middle name in full name

Question

I have a column which stores full name. I need to parse this in First name, last name, middle name , prefix etc.

Req for middle name is to pick name which is between space and then name with A-z Chars and then space. Foe ex:

Name- My name is 
Middle name-> name

Full name-> My n3ame is this
Middle Name-> is

Full name-> My name
Middle name-> NULL

I am not considering scenario where this double space is present twice for now. I am picking only the first occurrence in that case for now:

Ex:

Full Name-> My name is this
Middle name-> name

I was thinking below ( but this does not take care of middle name having only A-Z data, scenario 2 from above ex will give 'n3me' in this case instead of 'is'):

SUBSTR(FULL_name,Instr(Full_name,' '),Instr(Full_name,' ',2))

score 2 · Answer 1 · answered Apr 26 '19 at 17:13

Since you must exclude "words" (like n3ame) that aren't 100% alphabetic, it's easier to do this with regular expressions. Here's one way:

with t(full_name) as (
  select 'My name is'       from dual union all
  select 'My n3ame is this' from dual union all
  select 'My name'          from dual   
)
select full_name, 
       regexp_substr(full_name, '^.*? ([[:alpha:]]+) ', 1, 1, null, 1) middle_name
from   t
;

FULL_NAME          MIDDLE_NAME     
----------------   ----------------
My name is         name            
My n3ame is this   is              
My name

This will return the first occurrence of a string of 1 or more consecutive letters found between spaces.

Barbaros Özhan · Answer 2 · 2019-04-26T17:51:48.890

You can use regexp_substr :

with t(full_name) as
(
 select 'My name is' from dual union all
 select 'My n3ame is this' from dual union all
 select 'My name' from dual   
)    
select 
      case when (regexp_substr(full_name, '[^ ]+', 1, 3) is null ) then
           regexp_substr(full_name, '[^ ]+', 1, 3)
      else
         case when (regexp_like(full_name, '\d')) then
           regexp_substr(full_name, '[^ ]+', 1, 3)
         else
           regexp_substr(full_name, '[^ ]+', 1, 2) 
         end  
      end as "Middle Name"                                          
 from t;

Middle Name
-----------
name
is
<NULL>

which considers the first word after first space, provided there are at least three words in a full_name.

Demo

If I understood the question correctly (as demonstrated also by the desired outcome in the second example, which is different in your output), words that are not 100% alphabetic should be ignored. — , Apr 26 '19 at 17:14

Parse String data to pick only middle name in full name

2 Answers2