Help with a Parse and format name UDF

[kxramse]

I found a pretty awesome UDF to parse a name, but the creator hasn't been very responsive to requests to fix a hole that I found. Anyway, I leave my contract in two weeks and I need to get a solution to them on this before then.

Any chance someone could look at this code and tell me what it would take to allow it to accomidate a three part name like this: "De La Hoya, Oscar" while still leaving the rest of its functions in tact?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[fnFormatName](@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) as
begin
-- Keith Ramsey - 8/18/2006
-- Found from:  [URL unfurl="true"]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=56499[/URL]
--FormatName parses a NameString into its component parts and returns it in a requested format.
--
--@NameString is the raw value to be parsed.
--@NameFormat is a string that defines the output format.  Each letter in the string represents
--a component of the name in the order that it is to be returned.
--	[H] = Full honorific
--	[h] = Abbreviated honorific
--	[F] = First name
--	[f] = First initial
--	[M] = Middle name
--	[m] = Middle initial
--	[L] = Last name
--	[l] = Last initial
--	[S] = Full suffix
--	[s] = Abbreviated suffix
--	[.] = Period
--	[,] = Comma
--	[ ] = Space

--Example: select dbo.Formatname('Reverend Gregory Robert Von Finzer Junior', 'L, h. F m. s.')
--Result: 'Von Finzer, Rev. Gregory R. Jr.'

--Test variables
-- declare	@NameString varchar(50)
-- declare	@NameFormat varchar(20)
-- set	@NameFormat = 'L, h. F m. s.'
-- set	@NameString = 'Reverend Gregory Robert Von Finzer Junior'

Declare	@Honorific varchar(20)
Declare	@FirstName varchar(20)
Declare	@MiddleName varchar(30)
Declare	@LastName varchar(30)
Declare	@Suffix varchar(20)
Declare	@TempString varchar(100)
Declare	@IgnorePeriod char(1)

--Prepare the string
--Make sure each period is followed by a space character.
set	@NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
--Eliminate double-spaces.
while  charindex('  ', @NameString) > 0 set @NameString = replace(@NameString, '  ', ' ')
--Eliminate periods
while  charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')

--If the lastname is listed first, strip it off.
set	@TempString = rtrim(left(@NameString, charindex(' ', @NameString)))
if	@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))
if	right(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)
if	len(@LastName) > 0 set	@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

--Get rid of any remaining commas
while  charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')

--Get Honorific and strip it out of the string
set	@TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
if	@TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'CPL', 'Corporal', 'SGT', 'Sergeant', 'GEN', 'General', 'CMD', 'Commander',  'CPT', 'CAPT', 'Captain', 'MAJ', 'Major', 'PVT', 'Private', 'LT', 'Lieutenant', 'FATHER', 'SISTER') set @Honorific = @TempString
if	len(@Honorific) > 0 set	@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

--Get Suffix and strip it out of the string
set	@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if	@TempString in ('Jr', 'Sr',  'II', 'III', 'Esq', 'Junior', 'Senior', 'IV') set @Suffix = @TempString
if	len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))

if @LastName is null
begin
	--Get LastName and strip it out of the string
	set	@LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
	set	@NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))
	--Check to see if the last name has two parts
	set	@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
	if	@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
		begin
		set @LastName = @TempString + ' ' + @LastName
		set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
		end
end

--Get FirstName and strip it out of the string
set	@FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
set	@NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))

--Anything remaining is MiddleName
set	@MiddleName = @NameString

--Create the output string
set	@TempString = ''
while len(@NameFormat) > 0
begin
	if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.'
	begin
	set @IgnorePeriod = 'F'
	set @TempString = @TempString +
		case ascii(left(@NameFormat, 1))
			when '72' then case @Honorific
				when 'Dr' then 'Doctor'
				when 'Rev' then 'Reverend'
				when 'Hon' then 'Honorable'
				when 'Maj' then 'Major'
				when 'Pvt' then 'Private'
				when 'Lt' then 'Lieutenant'
				when 'Capt' then 'Captain'
				when 'Cpt' then 'Captain'
				when 'Cmd' then 'Commander'
				when 'Gen' then 'General'
				when 'Sgt' then 'Sergeant'
				when 'Cpl' then 'Corporal'
				else isnull(@Honorific, '')
				end
			when '70' then isnull(@FirstName, '')
			when '77' then isnull(@MiddleName, '')
			when '76' then isnull(@LastName, '')
			when '83' then case @Suffix
				when 'Jr' then 'Junior'
				when 'Sr' then 'Senior'
				when 'Esq' then 'Esquire'
				else isnull(@Suffix, '')
				end
			when '104' then case @Honorific
				when 'Doctor' then 'Dr'
				when 'Reverend' then 'Rev'
				when 'Honorable' then 'Hon'
				when 'Major' then 'Maj'
				when 'Private' then 'Pvt'
				when 'Lieutenant' then 'Lt'
				when 'Captain' then 'Capt'
				when 'Cpt' then 'Capt'
				when 'Commander' then 'Cmd'
				when 'General' then 'Gen'
				when 'Sergeant' then 'Sgt'
				when 'Corporal' then 'Cpl'
				else isnull(@Honorific, '')
				end
			when '102' then isnull(left(@FirstName, 1), '')
			when '109' then isnull(left(@MiddleName, 1), '')
			when '108' then isnull(left(@LastName, 1), '')
			when '115' then case @Suffix
				when 'Junior' then 'Jr'
				when 'Senior' then 'Sr'
				when 'Esquire' then 'Esq'
				else isnull(@Suffix, '')
				end
			when '46' then case right(@TempString, 1)
				when ' ' then ''
				else '.'
				end
			when '44' then case right(@TempString, 1)
				when ' ' then ''
				else ','
				end
			when '32' then case right(@TempString, 1)
				when ' ' then ''
				else ' '
				end
			else ''
		end
		if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER'))
			or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III','IV')))
			set @IgnorePeriod = 'T'
	end
	set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
end

Return @TempString
end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

I asked the original programmer (link included above) if he had any ideas as to why it can't handle a three part name and I was given a less than cooperative answer about how he didn't program it to do that yet. Even if someone was able to narrow my problem down to a specific line or to of the script so that I can pound out different variations that would help.

Thanks,
Keith