Oracle split string and update in new column

Question

I have 3 columns to capture a person's name: first_name, middle_name, and last_name. I have several entries where the first and middle names are in the first_name column.

first_name   | middle_name | last_name
------------------------------------
James Leroy  |             | Bond

I need to move the middle name into the middle_name column so my table looks like this.

first_name | middle_name | last_name
------------------------------------
James      | Leroy       | Bond

Answer 1

This task is not complex enough to require a regex. Simple string functions can be used, and should probably be preferred, since they are less expensive:

update mytable
set 
    first_name = substr(first_name, 1, instr(first_name, ' ') - 1),
    last_name  = substr(first_name, instr(first_name, ' ') + 1)
where instr(first_name, ' ') > 0