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

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 & A开发者_Python百科nna Smith" ---> "John & Anna" in column1 and "Smith" in column2

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;

For Example: "John Smith" --> "John" in column1 and "Smith" in column2 "John & Anna Smith" ---> "John & Anna" in column1 and "Smith" in column2

You will have to come up with the rules or set of rules(to cover additional possibilities) to be able to crop the names out. One way could be to maintain the rules in some config/xml/table, so that you can add the rules there, even if you come across other types of separators/delimiters or conditions.

You can start with look for last space in a name. For instance, in case of the example above, you are splitting the text with the last space in a string.