CODEWITHSUNDEEP
mysqlsqlmysql-workbench
ERGold
ERGold

Reputation: 1

seperate full name into first name and last name MySQL

I started my first year of learnig software development and there is a question I just can't solve, and sadly because of the corona virus I can't ask a teacher util next week. I have a table with full names and I need to separate them into a first name and a last name I have found some solutions that use SUBSTRING_INDEX but I haven't red about it anywhere yet. I somehow found a way to get the last name, but I just can't get the first name done. This is how I did the last name:

SELECT RIGHT(name, LENGTH(name) - INSTR(name, " ")) AS lastname FROM student;

Is there anybody that can help me with this? Thanks in advance

Upvotes: 0

Views: 200

Answers (2)

Ankit Jindal
Ankit Jindal

Reputation: 4030

Use SUBSTRING_INDEX to get everything at the left of the first space as one value, and everything past the first space as other value:

SELECT substring_index(name, ' ', 1) AS firstname,
       substring_index(name, ' ', -1) AS lastname

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Use substring_index():

select substring_index(name, ' ', 1) as first_name,
       substring_index(name, ' ', -1) as last_name

This assumes that the name has only two parts.

Upvotes: 2

Related Questions