Today, I was working on a existing large book library project. I was told to show a HTML report for all the books present on the library and also directed by mentor that only last name to be shown for the author names. But here problem is that the database doesn’t have a specific column which stores last name of a author. So, tried 2-3 ways for this but none of the way succeeded to solve the problem, although all of them were showing correct result ,when the author has a first name and a last name. But, in India most of the people have a middle name also. So, when the proc or, function encountered a name with 1 or more middle name(s) , resulted in wrong data. Thus, finally I arranged the following function which solved my problem.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
— Author: Suvendu
— Description: gets last name from supplied full name
CREATE FUNCTION [dbo].[GetLastName]
DECLARE @LastName NVARCHAR(30)
SELECT @LastName = RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(‘ ‘, REVERSE(@FullName))-1, -1),LEN(@FullName)))