I have a column "name" that contain first and last names. Some entries have 3 or 4 first names. Examples of names are:

         "Max Mustermann" 
         "Max Christian Mustermann"  
         "Max Christian von Mustermann"

I need to split those names into two seperate columns "firstname" and "lastname". last name I get with the following statement I found on stack overflow:

UPDATE table SET 
lastname = regexp_replace(name, '^.* ', '')

For the first name I need to return everything except that? At the moment I'm trying to read into the regexp_replace function but I find it hard to understand. I would appreciate some help.

CodePudding user response：

Modify the regex to cut everything after the last space inclusively:

update my_table set
    firstname = regexp_replace(name, '(^.*) .*', '\1'),
    lastname = regexp_replace(name, '^.* ', '')

Test it in db<>fiddle.

CodePudding user response：

How about using SUBSTRING with a regex pattern.

update your_table
set first_name = substring(name, '^(\w )')
  , last_name  = substring(name, '^\w \s (.*)$')
where (first_name is null or last_name is null);

select name, first_name, last_name
from your_table;

name	first_name	last_name
Max Mustermann	Max	Mustermann
Max Christian Mustermann	Max	Christian Mustermann
Max Christian von Mustermann	Max	Christian von Mustermann

Test on db<>fiddle here

CodePudding user response：

Using string operators left/right you could do:

select left(names, length(names)-pos) firstname, right(names,pos-1) Surname
from t,
lateral (values(strpos(reverse(names), ' ')))v(pos);

example Fiddle

CodePudding user response：

Are you considering split_part?

UPDATE my_table SET 
firstname = split_part(name, ' ', 1), lastname = split_part(name, ' ', 2)

Test it in dbfiddle