How to seperate last name and first names into two new columns in mysql?

Question

How do I seperate the Last Name and First Name into new columns?

I have "John & Anna Smith" in one column, I need to separate it into "John & Anna" & "Smith" in two columns. How do I do this?

There is no delimiter when we go from left. While we go from right the delimiter is space. The last sub-string is always the Last Name. The rest is the First Names.

For Example:

"John Smith" --> "John" in column1 and "Smith" in column2

"John & Anna Smith" ---> "John & Anna" in column1 and "Smith" in column2

Answer 1

Hey, you can try something like this

SELECT REPLACE('John & Anna Smith', SUBSTRING_INDEX('John & Anna Smith', ' ', -1),'') AS first_name, SUBSTRING_INDEX('John & Anna Smith', ' ', -1) AS last_name;