Get last name from existing full name using user defined function in SQL Server

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
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Suvendu
— Description: gets last name from supplied full name
— =============================================
CREATE FUNCTION [dbo].[GetLastName]
(
@FullName NVARCHAR(60)
)
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @LastName NVARCHAR(30)
SELECT @LastName = RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(‘ ‘, REVERSE(@FullName))-1, -1),LEN(@FullName)))
RETURN @LastName
END
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s