EXPERT RESPONSE
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.
|
