CODEWITHSUNDEEP
mysql
philipp
philipp

Reputation: 57

MySQL Split String in Column

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.

Upvotes: 0

Views: 252

Answers (1)

Tech Savant
Tech Savant

Reputation: 3766

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

Upvotes: 1

Related Questions