CODEWITHSUNDEEP
mysql
Shehary
Shehary

Reputation: 9992

Fetch values from column and split

I'm trying to fetch names from table where

Table

Id        Name 
1         John Doe
2         Tom Cruise
3         Christopher Ashton Kutcher
4         Mel Gibson
5         David Jude Law
6         Brad Pitt

My approach is

SELECT
    SUBSTRING_INDEX(Name, ' ', 1) AS FirstName,
    SUBSTRING_INDEX(Name, ' ', -1) AS LastName,
FROM Table

But In above table there are some names having middle names too so with my approach I'm missing the First name, e.g for name Christopher Ashton Kutcher I'm getting Ashton Kutcher as output.

Note: It doesn't matter if LastName will have middle name too in output as long as I get FirstName too.

So what will be the work around?

Upvotes: 0

Views: 55

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

As you don't care if middle name is mixed up with last name and you are splitting on ' ', this should work.

select substring(name, 1, instr(name,' ')-1) as first_name,
       replace(name, substring(name, 1, instr(name,' ')-1), '') as last_name
from table;

Fiddle

Upvotes: 1

Related Questions