CREATE function FormatName(@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--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') 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')))
			set @IgnorePeriod = 'T'
	end
	set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
end

Return @TempString
end

quote:

---

Originally posted by blindman

I can't believe I left those titles out!
I'll add them in and post the new code as soon as I get a chance.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---