Skip to toolbar

Forum

Find last name of a...
 
Notifications
Clear all

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


vinodkrsetty
Posts: 15
Admin
(@vinodkrsetty)
Member
Joined: 1 year ago

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

1 Reply
Rakesh
Posts: 41
 Rakesh
Guest
(@Rakesh)
Joined: 2 months 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.

Reply

Leave a reply

Author Name

Author Email

Title *

Maximum allowed file size is 10MB

 
Preview 0 Revisions Saved
Share: