Search code examples
sqlregexpostgresqldatabase-migration

How to migrate name column to first and last name in PostgreSQL

I want to write an SQL migration to split a "name" column to a "first_name" and a "last_name" column in a "users" table, I have already created the 2 columns.

I am looking for a command that looks like

UPDATE users
SET (first_name, last_name)
VALUES ({expression for first name}, {expression for last name});

I am accepting the assumption that the frontier between the 2 parts is the first whitespace character. (i.e. for John Doe Jr, the last name should be 'Doe Jr')

I have tried to use regexp_match(name, [^\s]*) for the first name but for name="John Doe" it returns first_name="{John}", how could I fix this, or is there a better way than using regexp_match?

Solution 
  • UPDATE users SET first_name = (regexp_split_to_array(name, E'\s+'))[1], last_name = (regexp_split_to_array(name, E'\s+'))[2]

    for names where 2 and more last names

    update users set firstname = (regexp_split_to_array(name, E'\\s+'))[1], 
        lastname = array_to_string((regexp_split_to_array(name, E'\\s+'))[2:], ' ')