I have a the following table structure
Joe Jay Blog Pepper
Jane Jay Bibs Salt
Jim Jack Jones Snr Sugar
What I am trying to do is take the value in first name and take everything before the first space into "first", everything between the first and second spaces and move it to "middle" and everything after the 3rd space in "end".
So for my last record:
first = Jim
second = Jack
third = Jones Snr
I can satisfy my first requirement with the following below:
SELECT SUBSTR(FirstName, 1,LOCATE(' ',FirstName)) AS first
But that seems to be as fair as I am currently.
This seems to work :P
First get firstname and group the middle+lastname, and just do the same again with the result to get the middlename with
LEFT and lastname with right.
SELECT name, firstname, /*already got this one*/ LEFT(middle_last_name, INSTR(middle_last_name, ' ')-1) AS middlename, /*do the same LEFT to get middlename*/ RIGHT(middle_last_name, LENGTH(middle_last_name)-INSTR(middle_last_name, ' ')) AS last_name /*get the remaining chars for lastname*/ FROM ( SELECT *, LEFT(name, INSTR(name, ' ')-1) AS firstname, /*Simply get the firstname with left*/ RIGHT(name, LENGTH(name)-INSTR(name, ' ')) AS middle_last_name /*group the rest for this sub-query*/ FROM users );