Split a name field in Oracle into First and Last Name

I've got a table with names in it, which looks like this:

Emp_ID     Emp_Name
0001       Jack Skelly
0002       Susan Ryan

I need to produce something that looks like this:

Emp_ID    Emp_F_Name    Emp_L_Name
0001      Jack          Skelly
0002      Susan         Ryan

I've come up with this to split the name:

SubStr(d.Name, 0, InStr(d.Name, ' ')-1) as FName, 
SubStr(d.Name, InStr(d.Name, ' ')+1) as LName

I've seen people suggest INSTR, I've seen people suggest regexp_instr and I've seen them suggest regexp_substr. I'm still new to Oracle and I don't really understand either, or which (if either) of them is more efficient for the split I need to do. Can someone tell me the best way to go about splitting this field? Is the method I've used the best/most efficient for what I want to do?

Solution

substr and instr are almost always faster/more efficient than regular expression functions.

INSTR is used to find the position of the space in the input string. SUBSTR is used to extract a substring from the input string. Search (google) the Oracle documentation for these functions - you will find detailed explanations and examples.

The bigger issue you will have with your problem is the business requirement - what do you do with names like "John Clayton Stuart", "Ali Abu Ahmad", or single-word like "Belphegor"?

The query below shows how easily you can solve the problem if all the names have the format "Firstname Lastname".

with
     input_table ( emp_id, emp_name ) as (
       select '0001', 'Jack Skelly' from dual union all
       select '0002', 'Susan Ryan'  from dual
     )
-- End of simulated input data (not part of the solution).
-- SQL query begins BELOW THIS LINE.
select emp_id, emp_name,
       substr(emp_name, 1, instr(emp_name, ' ') - 1) as first_name,
       substr(emp_name, instr(emp_name, ' ') + 1)    as last_name
from   input_table
;

EMP_ID EMP_NAME    FIRST_NAME  LAST_NAME 
----   ----------- ----------- -----------
0001   Jack Skelly Jack        Skelly     
0002   Susan Ryan  Susan       Ryan