Problem solve Get help with specific problems with your technologies, process and projects.

Extract surname from name column

I have table, Employee, with columns Number, Name, Phone. The Name column consists of first name and surname together, e.g John Smith. Is it possible to extract surname from Name column using Instr and Substr functions?

First of all, there's no easy way to handle all names. Period. You are advised to review everything that your query...

produces, because there's usually some value that you didn't count on. If all names are like John Smith and have only a single first name and a single surname, then it's easy, you just find the blank and split the substring into two strings at that point.

Unfortunately, real names are never so well behaved. You'll occasionally find single names, like Cher, or multiple names, like John Paul George Ringo Rockstar. The query needs to handle these cases as well.

And no matter what you do, you cannot simply assume that the last name is the surname, because this fails for people like Robert Van de Graaff and John Wilson III. It's easy for a human to see the surnames (Van de Graaff and Wilson respectively), but it's very hard to write a query that can do the same. So let's arbitrarily say that the rightmost name in the column is the surname, understanding full well that this will produce errors (Graaff, III) for some names.

So with this as the strategy, we simply need to find the last blank in the name column. In many databases, like MySQL and Microsoft SQL Server, you can use the REVERSE function for this purpose. In SQL Server, you would say:

 select case when charindex(' ',reverse(name))=0 then name else right(name ,charindex(' ',reverse(name))-1 ) end as surname from ...

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.

However, based on your question about using INSTR and SUBSTR,  you're using Oracle. In Oracle, you can use a handy feature of the INSTR function, to find the first blank from the right.

 select substr(name ,instr(name,' ',-1)+1 ) as surname from ...

Here, the -1 parameter of INSTR indicates that we're searching for the first occurrence going backwards 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.

This was last published in November 2004

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.