SQL Like query last match

Question

Hi,

I've a database that has a name field. (i.e Firstname M. Lastname or just Firstname Lastname).
Trying to filter by lastname.
How can I do a query to find the last space?

Something like
select * from person where name like "% a%" (but the space is the last space)

Thanks,
Tee

Answer 1

If this is mySQL, you can consider using the SUBSTRING_INDEX() function (with count = -1).

Answer 2

If using some version of Microsoft SQL Server, you could reverse() the string, and then use charindex() to find the first space.

Answer 3

SELECT CASE WHEN [Name] LIKE '% [^ ]%'
            THEN SUBSTRING([Name], 
                           LEN([Name]) - CHARINDEX(' ', REVERSE([Name])) + 1, 
                           8000)
            ELSE [Name]
       END AS [LastName]
FROM [Customers]