MySQL Split String in Column

Question

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.

possible duplicate: stackoverflow.com/questions/14950466/… — Alan Francis
– Alan Francis, Commented Jul 3, 2015 at 9:31

Tech Savant · Accepted Answer · 2015-07-03 09:46:24Z

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));

edited Jul 3, 2015 at 9:46

answered Jul 3, 2015 at 9:32

Tech Savant

3,7671 gold badge21 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

philipp Over a year ago

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.

Tech Savant Over a year ago

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! :)

Collectives™ on Stack Overflow

MySQL Split String in Column

1 Answer 1

Per Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Per Comments

2 Comments

Linked

Related