FirstName , LastName , and MiddleName functions for SQL Server

A common procedure when working with back office databases is to split up a Full Name field into its constituent First Name, Middle Name and Last Name. The following script will create 3 User Defined functions which will perform this function.

To demonstrate how to use these scripts I will use a GoldMine database which I imported into SQL server from the DBase files.

The following SQL statement will parse up the Contact field of the Contact1 table into separate First, Middle and Last Names.

Example SQL:

select contact
, dbo.FirstName(Contact) as fn
, dbo.LastName(Contact) as ln
, dbo.MiddleName(Contact) as mn
from dbo.Contact1

SQL Script

create function dbo.FirstName(@FullName as varchar(256))
returns varchar(256) as
begin
declare @Retval as varchar(256)
set @Retval=left(@FullName,charindex(' ',@FullName))
return @retval
end

create function dbo.LastName(@FullName as varchar(256))
returns varchar(256) as
begin
declare @Retval as varchar(256)
set @Retval= case when charindex(' ',reverse(@FullName))>0 then right(@FullName,charindex(' ',reverse(@FullName))-1) else '' end
return @Retval
end

CREATE FUNCTION dbo.MiddleName (@FullName as varchar(256)) 
RETURNS varchar(256)  AS 
BEGIN
declare @posFN as int
declare @posLN as int
declare @Retval as varchar(256)

set @posFN=charindex(' ',@FullName)+1
set @posLN=len(@FullNAme)-charindex(' ',reverse(@FullName))+1

set @Retval=''

if @posFN>1
begin
 if @posLN>1
 begin
  if @posLN-@posFN>0
  begin
   set @Retval=substring(@FullName,@posFN,@posLN-@posFN)
  end
 end
end

return @Retval
END