Seperate first and last names from single column

I have a person table where the name column contains names, some in the format “first last” and some in the format “first”.

My query

SELECT name,
SUBSTRING(name FROM 1 FOR POSITION(' ' IN name) ) AS first_name
FROM person

​x
 
SELECT name,SUBSTRING(name FROM 1 FOR POSITION(' ' IN name) ) AS first_nameFROM person​

creates a new row of first names, but it doesn’t work for the names which only have a first name and no blank space at all.

I know I need a CASE statement with something like 0 = (' ', name) but I keep running into syntax errors and would appreciate some pointers.

Answer

SELECT split_part(name, ' ', 1) AS first_name
     , split_part(name, ' ', 2) AS last_name
FROM person;

 
SELECT split_part(name, ' ', 1) AS first_name     , split_part(name, ' ', 2) AS last_nameFROM person;​