Find last name of a...
Clear all

Find last name of all employees from a full name column?

Posts: 27
Topic starter
Eminent Member
Joined: 5 years ago

Please suggest how to Find the last name of all employees from a full name column?

1 Reply
Posts: 47
Eminent Member
Joined: 4 years ago

In MySQL, you'd use LOCATE instead of CHARINDEX. The idea here is that when the name is reversed, the location of the first blank going forward in the reversed name is 1 more than the number of characters that you want to extract from the right of the original, unreversed, name.

The CASE expression covers the situation where the name contains no blank at all, e.g. Cher.

select substr(name ,instr(name,' ',-1)+1 ) as LastName from Table_Name

Here, the -1 parameter of INSTR indicates that we're searching for the first occurrence going backward from the end of the column. SUBSTR usually requires a starting position and a length, but if the length is omitted, the substring extends to the end of the column.

Note that if INSTR finds no blank, it returns 0, but if 0 is the starting position in SUBSTR, it is treated as 1.


Self Learning Video Tutorials - Software Course

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved