I wrote a piece on the new SUBSTRING in SQL Server 2025 and got asked a question. How do we get the last last name, such as only getting “Paolino” from “Miguel Angel Paolino”. This post will show how you can easily do this.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
I have a set of names, like those in the Northwind.dbo.Customers table. I want to find the last names only, perhaps for a mailing, or maybe for a search box. I have names like these:
Notice line 80 above. There are three names here. In the US, we might consider this as a first, middle, and last names. In Spain, however, this might be a first name and two surnames. If I only wanted the last last name (Paolino), how can I get that?
One of the cool things about working with strings is that we can look at them a few ways, and we have a great T-SQL function that can help: REVERSE(). The last last name is really the first name in a reversed string.
Backwards, but we can fix that.
Let me build up a query. First, I’ll get the ContactName and then the First Name. I’ll use the Charindex to find a space and then assume everything before the space is the first name. That gives me this code:
SELECT
ContactName,
SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName
FROM dbo.Customers;
And these results. Notice I have the first names. This isn’t perfect, but it’s often works.
Now, let’s add the string reversed.
SELECT
ContactName,
SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
REVERSE(ContactName) AS ReversedName
FROM dbo.Customers;
The results are interesting. Look at lines 79 and 80. The first name is the first word before a space. For the last name, it’s the first word before a space, but reversed. The first part of 79 is shpesoJ and the first part of 80 is oniloaP.
So let’s repeat our substring on the reversed string. Here’s new code:
SELECT
ContactName,
SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
SUBSTRING(REVERSE(ContactName), 1, CHARINDEX(' ', REVERSE(ContactName))) AS ReversedLastName
FROM dbo.Customers;
And look at the results. now my third column is the last name, just backwards.
Now we can wrap that last column in another REVERSE() and we get the results we want.
SQL New Blogger
This is a common type of task, and one that you might be asked in an interview, or as a part of a spec. This post only took about 10 minutes to write, with code, and if this were on your blog, I bet an interviewer would ask you how to do this.
Try to influence the interview and write your own post. Do some testing on performance as well, explore how to work with T-SQL to become better at it and showcase this to your next hiring manager.
