0

I've got a column in a mysql table which contains name information:

"Fred Barney Feuerstein", for example.

Now I need to split this string to create a view with two columns - firstname, lastname. I know how to select the lastname:

select (SUBSTRING_INDEX(name, ' ', -1)) as lastname from contacts;

But I don't know how to extract all the other information to one new field. What I'm searching for is something like the SUBSTRING_INDEX for everything except the last field.

atoMerz
  • 7,534
  • 16
  • 61
  • 101
philipp
  • 57
  • 4

1 Answers1

1
//First Item
SUBSTRING_INDEX(`name`, ' ', 1)), 1)

//Second Item
SUBSTRING_INDEX(SUBSTRING_INDEX(`name`, ' ', 2), ' ', -1)), 1)

Per Comments

How to get the first two names...

substr(`name`, 1, (length(`name`) - length(SUBSTRING_INDEX((`name`), ' ', -1))-1));
Tech Savant
  • 3,686
  • 1
  • 19
  • 39
  • Thank you - you are right but i need to define the last part und everything what is not in the last because of columns like this: "Fred Barney Miller" "Michael Edgar Jason" "Christian Schmitz" In these examples Fred Barney are the firstnames and Miller the last name. But there could be also fields were firstname is just one name. – philipp Jul 03 '15 at 09:40
  • Try my edit... let me know if you need help getting just the last name. If my answer is correct can you accept it by clicking the checkmark to the left of my answer. Thanks! :) – Tech Savant Jul 03 '15 at 09:47