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

```sql
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`?