SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Parse and format name strings.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2005 :  11:51:34  Show Profile  Reply with Quote
Please let me know if you come across any name strings this function cannot parse.
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

greenmtnsun
Starting Member

USA
13 Posts

Posted - 08/18/2006 :  15:35:11  Show Profile  Reply with Quote
I think I found two things that do not work; I might have resolved one.

Last name "De La Cruz" passed into your UDF dbo.FormatName(LNAME, 'L') The result I get is Cruz

Also, is there a reason you did not include 'IV' as a suffix? I saw how to add it, but I wondered if I might have missed something since it seemed to easy to add.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/18/2006 :  16:20:06  Show Profile  Reply with Quote
Thanks. I'll try to add these.
But frankly, "IV"? That's getting a bit sychophantic for any family...
Go to Top of Page

greenmtnsun
Starting Member

USA
13 Posts

Posted - 08/18/2006 :  16:36:34  Show Profile  Reply with Quote
I can agree with you, but users add all kinds of crazy data.

The Suffix of IV seems easy, but let me know if I didn't think of something because I'd hate to have it cut off the name "Ivan".

Just add IV to this:
if @TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior', 'IV') set @Suffix = @TempString

and add IV to this:
if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III','IV')))

The name "De La Hoya" though is not as easy to resolve. Any assistance would be greatly apprecaited.

I forgot to say this earlier, but your script is awesome!

Keith
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/18/2006 :  21:30:56  Show Profile  Reply with Quote
Your code addition should work fine. The code works on whole blocks of characters, so it should not affect names containing "iv".
Go to Top of Page

greenmtnsun
Starting Member

USA
13 Posts

Posted - 08/21/2006 :  12:01:21  Show Profile  Reply with Quote
Kewl. So that means if someone ever wanted to add V, VI, VII, VIII, IX and X they could. Like I said, its a awesome script.

Any ideas as to why it can't handle a three part name?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/21/2006 :  13:16:59  Show Profile  Reply with Quote
Uhm...'cause I didn't code it to handle three-part last names. It deals only with discrete strings. Most of these multi-part names will have to be handled with one-offs of one-off lists. When I update it, I'll post a new copy.
Go to Top of Page

rasmuscm
Starting Member

1 Posts

Posted - 09/05/2006 :  22:11:03  Show Profile  Reply with Quote
This is great!

The only issue I ran into was that we seem to have a lot of Ph.D.'s and that messed it up. But thanks!
Go to Top of Page

hminot
Starting Member

USA
1 Posts

Posted - 11/17/2006 :  09:51:51  Show Profile  Reply with Quote
Thank you for this UDF! It works great for me!

Harvey Minot
Go to Top of Page

jaykoni
Starting Member

1 Posts

Posted - 12/21/2007 :  16:11:29  Show Profile  Reply with Quote
FANTASTIC code. It didn't do everything I wanted, but it was a wonderful start for me.

I converted it to a procedure and added a dependency to a word count function (on this site) and am acheiving about 99.99% accucury from a single name field.

Here is my code:

USE Jay
go

ALTER PROCEDURE NameParser(
@NameString VARCHAR(100) = NULL
, @Honorific VARCHAR(30) OUTPUT
, @FirstName VARCHAR(30) OUTPUT
, @MiddleName VARCHAR(30) OUTPUT
, @LastName VARCHAR(30) OUTPUT
, @Suffix VARCHAR(30) OUTPUT
)
AS
BEGIN
-- Dependent on: GetWordCount function (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=51289)
-- Dependent on: A Case insenitive collation

--blindman, 11/04
--FormatName parses a NameString into its component parts and returns it in a requested format.

-- Jay - 12/2007 Completly re-written as a procedure with 4 OUTPUT parameters
-- Remove anything in parens
-- Support for 3-part names (Van Der, De La)
-- Support for multi-part suffix ('MD, FACS', 'III, MD', etc)
-- Various prefixs & suffixs (including some suffixs to clean up company names)
-- If name matches an email address, returns email in the Last Name
-- Strip numerics, &;#:/"*-()
-- Strip stray (space), -, * from beginning/end of parsed name parts
-- Procedure now dependent on GetWordCount() function
-- Deals with C/O in a name
-- General rules:
-- - Begins (or ends) with c/o, just ignore it
-- - Prefer the name before the c/o
-- - if before is more than 3 parts and after is 2, or 3 parts - use after
-- (May change depending on results, will attempt to make code easilly changable)
-- Converted to a procedure that uses OUTPUT
--
-- Added logic to "Last, First" names to include 3-part names and also act on the "," if there is no valid suffix.
-- Moved possible Honorific, Suffix, 2-part last names and 3-part last names to a (local) table structure.
-- If someone wants to, they can easilly move these values to real tables and maintain them in a seperate UI.
--


DECLARE @LastSuffix varchar(20)
DECLARE @TempString varchar(100)
DECLARE @TempString2 varchar(100)
DECLARE @SaveNameString varchar(100)
DECLARE @IgnorePeriod char(1)
DECLARE @WordCount int
DECLARE @Part1 varchar(20)
DECLARE @Part2 varchar(20)
DECLARE @ThreePart char(1)
DECLARE @CO1IsCo char(1)
DECLARE @CO2IsCo char(1)
DECLARE @CO1words TINYINT
DECLARE @CO2words TINYINT

DECLARE @CO1Honorific varchar(30)
DECLARE @CO1FirstName varchar(30)
DECLARE @CO1MiddleName varchar(30)
DECLARE @CO1LastName varchar(30)
DECLARE @CO1Suffix varchar(30)

DECLARE @CO2Honorific varchar(30)
DECLARE @CO2FirstName varchar(30)
DECLARE @CO2MiddleName varchar(30)
DECLARE @CO2LastName varchar(30)
DECLARE @CO2Suffix varchar(30)

declare @OpenP tinyint
declare @CloseP tinyint
declare @Space1 tinyint
declare @Space2 tinyint

SET @Honorific = NULL
SET @FirstName = NULL
SET @MiddleName = NULL
SET @LastName = NULL
SET @Suffix = NULL

if @NameString IS NULL RETURN

-- Prefix table
DECLARE @Prefixs TABLE (
Prefix varchar(30)
)

insert into @Prefixs (Prefix) VALUES('Mr')
insert into @Prefixs (Prefix) VALUES('Mrs')
insert into @Prefixs (Prefix) VALUES('MrMrs') -- In data as 'Mr & Mrs'
insert into @Prefixs (Prefix) VALUES('Ms')
insert into @Prefixs (Prefix) VALUES('Miss')
insert into @Prefixs (Prefix) VALUES('Dr')
insert into @Prefixs (Prefix) VALUES('Drs')
insert into @Prefixs (Prefix) VALUES('Doctor')
insert into @Prefixs (Prefix) VALUES('Rev')
insert into @Prefixs (Prefix) VALUES('Reverend')
insert into @Prefixs (Prefix) VALUES('Sir')
insert into @Prefixs (Prefix) VALUES('Hon')
insert into @Prefixs (Prefix) VALUES('Honorable')
insert into @Prefixs (Prefix) VALUES('CPL')
insert into @Prefixs (Prefix) VALUES('Corporal')
insert into @Prefixs (Prefix) VALUES('SGT')
insert into @Prefixs (Prefix) VALUES('Sergeant')
insert into @Prefixs (Prefix) VALUES('GEN')
insert into @Prefixs (Prefix) VALUES('General')
insert into @Prefixs (Prefix) VALUES('CMD')
insert into @Prefixs (Prefix) VALUES('Commander')
insert into @Prefixs (Prefix) VALUES('Cpt')
insert into @Prefixs (Prefix) VALUES('Capt')
insert into @Prefixs (Prefix) VALUES('Captain')
insert into @Prefixs (Prefix) VALUES('Maj')
insert into @Prefixs (Prefix) VALUES('Major')
insert into @Prefixs (Prefix) VALUES('PVT')
insert into @Prefixs (Prefix) VALUES('Private')
insert into @Prefixs (Prefix) VALUES('LT')
insert into @Prefixs (Prefix) VALUES('Lieutenant')
insert into @Prefixs (Prefix) VALUES('Father')
insert into @Prefixs (Prefix) VALUES('Sister')

-- Suffix table
DECLARE @Suffixs TABLE (
Suffix varchar(30),
Type char(4)
)

insert into @Suffixs (Suffix, Type) VALUES('Junior', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Senior', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Jr', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Sr', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('II', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('III', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('IV', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Esq', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CPE', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('GM', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('RN', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('BS', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('MD', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('M D', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('C H A.', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CHA', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('ST', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('DO', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('D O', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('PhD', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('ManagingDirector', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('MS', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('RD', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('LD', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('DO', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('PC', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('DPM', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('FACS', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('EdM', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Chairman', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CPM', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('LPN', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Esthetician', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('ARNP', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('PE', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('LSCW', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('DC', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CMA', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('JD', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('DMH', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('VP', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('FPMSI', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('PMB', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('PTY', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CMT', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('CFP', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('MBE', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('SKC', 'Name')
insert into @Suffixs (Suffix, Type) VALUES('Plc', 'Name')

insert into @Suffixs (Suffix, Type) VALUES('Ltd', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Co', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Inc', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Ent', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Tech', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Technology', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Technologies', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Systems', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('USA', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('LLC', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Environments', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Clinic', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Society', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Architects', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Communications', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Hospital', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Hosp', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('University', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Univ', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Associates', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Conference', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Assistant', 'Co')
insert into @Suffixs (Suffix, Type) VALUES('Intl', 'Co')

-- TwoPartNames table
DECLARE @Twoparts TABLE (
TwoPart varchar(30)
)
insert into @TwoParts (TwoPart) VALUES('VAN')
insert into @TwoParts (TwoPart) VALUES('VON')
insert into @TwoParts (TwoPart) VALUES('MC')
insert into @TwoParts (TwoPart) VALUES('Mac')
insert into @TwoParts (TwoPart) VALUES('DE')
insert into @TwoParts (TwoPart) VALUES('St')
insert into @TwoParts (TwoPart) VALUES('St.')
insert into @TwoParts (TwoPart) VALUES('Le')
insert into @TwoParts (TwoPart) VALUES('Di')
insert into @TwoParts (TwoPart) VALUES('La')
insert into @TwoParts (TwoPart) VALUES('Al')
insert into @TwoParts (TwoPart) VALUES('Da')
insert into @TwoParts (TwoPart) VALUES('El')
insert into @TwoParts (TwoPart) VALUES('Dal')
insert into @TwoParts (TwoPart) VALUES('Del')
insert into @TwoParts (TwoPart) VALUES('Sta')
insert into @TwoParts (TwoPart) VALUES('Ste')

-- ThreePartNames table
DECLARE @Threeparts TABLE (
ThreePart varchar(30)
)
insert into @ThreeParts (ThreePart) VALUES('de sta')
insert into @ThreeParts (ThreePart) VALUES('de ste')
insert into @ThreeParts (ThreePart) VALUES('de la')
insert into @ThreeParts (ThreePart) VALUES('del la')
insert into @ThreeParts (ThreePart) VALUES('van der')
insert into @ThreeParts (ThreePart) VALUES('van de')

--Strip any leading non-alphanumeric
while patindex('[^a-z0-9]%', @NameString) = 1 SET @NameString = SUBSTRING(@NameString, 2, LEN(@NameString))
--print 'NameString_01=[' + @NameString + ']'

-- If this is an email address, return it immediatly (Jay)
if @NameString LIKE '%@%.%'
begin
while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', '')
SELECT
@Honorific = ''
, @FirstName = ''
, @MiddleName = ''
, @LastName = @NameString
, @Suffix = ''

RETURN
end

-- Whack anything in ()'s -- The use is very variable and though it could be parsed, I'm chosing not to.
set @OpenP = CHARINDEX('(', @NameString)
set @CloseP = CHARINDEX(')', @NameString)
if @OpenP < @CloseP and @OpenP > 0
begin
--print 'Open: ' + CAST(@OpenP AS VARCHAR) + ' - ' + CAST(@CloseP AS VARCHAR)
set @NameString = substring(@NameString, 1, @OpenP-1) + ' ' + substring(@NameString, @CloseP+1, 999)
while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')
--print '[' + @NameString + ']'
end

-- Initial c/o parsing
if ( @NameString LIKE '%C\O%' ) set @NameString = replace(@NameString, 'c\o', 'C/O')
if ( @NameString LIKE '%C.O.%' ) set @NameString = replace(@NameString, 'c.o.', 'C/O')

if ( @NameString LIKE 'C/O%' )
set @NameString = SUBSTRING(@NameString, 4, LEN(@NameString))

if ( @NameString LIKE '%C/O%' )
begin
set @CO1words = dbo.GetWordCount(SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1), NULL)
set @CO2words = dbo.GetWordCount(SUBSTRING(@NameString, PATINDEX('%C/O%', @NameString)+3, LEN(@NameString)), NULL)
--set @CO1Suffix = dbo.FormatName(SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1), 's') -- XXX
--set @CO2Suffix = dbo.FormatName(SUBSTRING(@NameString, PATINDEX('%C/O%', @NameString)+3, LEN(@NameString)), 's') -- XXX Old function, make recursive

set @TempString = SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1)
EXEC
dbo.NameParser @NameString = @TempString
, @Honorific = @CO1Honorific OUTPUT
, @FirstName = @CO1FirstName OUTPUT
, @MiddleName = @CO1MiddleName OUTPUT
, @LastName = @CO1LastName OUTPUT
, @Suffix = @CO1Suffix OUTPUT

set @TempString = SUBSTRING(@NameString, PATINDEX('%C/O%', @NameString)+3, LEN(@NameString))
EXEC
dbo.NameParser @NameString = @TempString
, @Honorific = @CO2Honorific OUTPUT
, @FirstName = @CO2FirstName OUTPUT
, @MiddleName = @CO2MiddleName OUTPUT
, @LastName = @CO2LastName OUTPUT
, @Suffix = @CO2Suffix OUTPUT

-- Decide to use name 1, or name 2. Prefer a persons name.
if @CO1Suffix in (
SELECT Suffix
FROM @Suffixs
WHERE Type = 'Co'
)
or SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1) LIKE 'The %' -- Sometimes "The Smith's", but the rule will still apply
set @CO1IsCo = 'Y'
else
set @CO1IsCo = 'N'

if @CO2Suffix in (
SELECT Suffix
FROM @Suffixs
WHERE Type = 'Co'
)
or SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1) LIKE 'The %' -- Sometimes "The Smith's", but the rule will still apply
set @CO2IsCo = 'Y'
else
set @CO2IsCo = 'N'

if ( @CO1IsCo = 'Y' AND @CO2IsCo = 'N' )
begin
set @NameString = SUBSTRING(@NameString, PATINDEX('%C/O%', @NameString)+3, LEN(@NameString))
end
else
begin
if ( @CO1IsCo = 'N' AND @CO2IsCo = 'Y' )
begin
set @NameString = SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1)
end
else
begin
-- Are they both company names?
if ( @CO1IsCo = 'Y' AND @CO2IsCo = 'Y' )
begin
-- Use 1st
set @NameString = SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1)
end
else
begin
-- A 2, or 3 word name is prefered to a 1, 4, or more part name
if ( @CO1words = 2 or @CO1words = 3 ) and ( @CO2words = 1 or @CO2words >= 4 )
begin
set @NameString = SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1)
end
else
begin
if ( @CO2words = 2 or @CO2words = 3 ) and ( @CO1words = 1 or @CO1words >= 4 )
begin
set @NameString = SUBSTRING(@NameString, PATINDEX('%C/O%', @NameString)+3, LEN(@NameString))
end
else
begin
-- Give up and use 1st
set @NameString = SUBSTRING(@NameString, 1, PATINDEX('%C/O%', @NameString)-1)
end
end -- 2,3 word over 1, 4+
end -- IsCo Y/Y
end -- IsCo N/Y
end -- else IsCo Y/N
end

--Prepare the string
--Make sure each period and comma is followed by a space character.
set @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
set @NameString = rtrim(ltrim(replace(@NameString, ',', ', ')))

-- Catch Suffix's

--Eliminate periods
while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', ' ')
--Eliminate numerics
while charindex('0', @NameString) > 0 set @NameString = replace(@NameString, '0', ' ')
while charindex('1', @NameString) > 0 set @NameString = replace(@NameString, '1', ' ')
while charindex('2', @NameString) > 0 set @NameString = replace(@NameString, '2', ' ')
while charindex('3', @NameString) > 0 set @NameString = replace(@NameString, '3', ' ')
while charindex('4', @NameString) > 0 set @NameString = replace(@NameString, '4', ' ')
while charindex('5', @NameString) > 0 set @NameString = replace(@NameString, '5', ' ')
while charindex('6', @NameString) > 0 set @NameString = replace(@NameString, '6', ' ')
while charindex('7', @NameString) > 0 set @NameString = replace(@NameString, '7', ' ')
while charindex('8', @NameString) > 0 set @NameString = replace(@NameString, '8', ' ')
while charindex('9', @NameString) > 0 set @NameString = replace(@NameString, '9', ' ')
--Eliminate &
while charindex('&', @NameString) > 0 set @NameString = replace(@NameString, '&', ' ')
--Eliminate ;
while charindex(';', @NameString) > 0 set @NameString = replace(@NameString, ';', ' ')
--Eliminate :
while charindex(':', @NameString) > 0 set @NameString = replace(@NameString, ':', ' ')
--Eliminate #
while charindex('#', @NameString) > 0 set @NameString = replace(@NameString, '#', ' ')
--Eliminate /
while charindex('/', @NameString) > 0 set @NameString = replace(@NameString, '/', ' ')
--Eliminate (
while charindex('(', @NameString) > 0 set @NameString = replace(@NameString, '(', ' ')
--Eliminate )
while charindex(')', @NameString) > 0 set @NameString = replace(@NameString, ')', ' ')
--Eliminate "
while charindex('"', @NameString) > 0 set @NameString = replace(@NameString, '"', ' ')
--Eliminate *
while charindex('*', @NameString) > 0 set @NameString = replace(@NameString, '*', ' ')
--Eliminate _ -> (space)
while charindex('_', @NameString) > 0 set @NameString = replace(@NameString, '_', ' ')
--Eliminate double-spaces.
while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')
--Strip remaining spaces
SET @NameString = LTRIM(RTRIM(@NameString))

--print 'NameString_03=[' + @NameString + ']'

-- Change suffix's
-- 'M.D.' becomes 'M D' to 'MD' so the suffix code will catch it
if substring(@NameString, LEN(@NameString)-2, 3) = 'M D' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-3) + 'MD'
if substring(@NameString, LEN(@NameString)-3, 4) = 'Ph D' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-4) + 'PhD'
if substring(@NameString, LEN(@NameString)-2, 3) = 'D O' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-3) + 'DO'
if substring(@NameString, LEN(@NameString)-4, 5) = 'D P M' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-5) + 'DPM'
if substring(@NameString, LEN(@NameString)-6, 7) = 'F A C S' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-7) + 'FACS'
if substring(@NameString, LEN(@NameString)-16, 17) = 'Managing Director' set @NameString = SUBSTRING(@NameString, 1, LEN(@NameString)-17) + 'ManagingDirector'

-- If the lastname is listed first, strip it off.
-- Only do this if the comma isn't in position 2. Trying to avoid single character last names and people using a , when a . is correct (or Europeian)
if patindex('_,%', @NameString) = 0 and patindex('%,%', @NameString) > 0
begin
set @Space1 = charindex(' ', @NameString)
-- Catch 3-part last names in "Last, First" format -> 'De la Rosa, Marie'
if patindex('% % %', @NameString) > 0
begin
set @Space2 = charindex(' ', @NameString, @Space1 + 1)
set @TempString = substring(@NameString, 1, @Space2-1)
if @TempString IN ( SELECT ThreePart FROM @ThreeParts )
set @Lastname = substring(@NameString, 1, charindex(',', @NameString))
end

if @Lastname IS NULL
begin
-- Catch 2-part names in "Last, First" format -> Von Hussan, Mike
set @TempString = rtrim(left(@NameString, charindex(' ', @NameString))) -- Gets the 1st word in the string
if @TempString in ( SELECT TwoPart FROM @TwoParts )
set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))

if right(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)
end

if len(@LastName) > 0
begin
set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
end
else
begin
-- Could be some other "Last, First" if there isn't a suffix on the name
set @TempString2 = reverse(@NameString)
set @TempString = rtrim(ltrim(reverse(substring(@TempString2, 1, charindex(',', @TempString2)-1))))
if @TempString NOT in (
SELECT Suffix FROM @Suffixs
)
begin
set @LastName = substring(@NameString, 1, charindex(',', @NameString)-1)
set @NameString = ltrim(rtrim(substring(@NameString, charindex(',', @NameString)+1, 999)))
end
end
end

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

--print '[' + @NameString + ']'
--Get Honorific and strip it out of the @NameString
if dbo.GetWordCount(@NameString, NULL) >= 2
begin
-- Manual adjustments
if @NameString like 'mrss[., ]' set @NameString = 'Mrs ' + SUBSTRING(@NameString, 6, LEN(@NameString))
if @NameString like 'Mr Mrs %' set @NameString = 'MrMrs ' + SUBSTRING(@NameString, 8, LEN(@NameString))
set @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
if @TempString in (
SELECT Prefix FROM @Prefixs
)
set @Honorific = @TempString

-- Manual adjustments
if @Honorific = 'MrMrs' set @Honorific = 'Mr & Mrs'

if len(@Honorific) > 0
begin
set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
end
end
--print 'NameString_05=[' + @NameString + '], H=[' + ISNULL(@Honorific, '') + '], F=[' + ISNULL(@FirstName, '') + '], M=[' + ISNULL(@MiddleName, '') + '], L=[' + ISNULL(@LastName, 'NULL') + '], S=[' + ISNULL(@Suffix, '') + ']'


-- Deal with multiple suffixs (Jay)
--Get Suffix and strip it out of the string
if dbo.GetWordCount(@NameString, NULL) >= 2 and NOT (dbo.GetWordCount(@NameString, NULL) = 3 and @NameString LIKE '% [A-Z] %')
begin
set @Suffix = ''
set @LastSuffix = ''
while 1 = 1
begin
-- Manual suffix adjustments
if @NameString LIKE '%[ .,]M D' SET @NameString = substring(@NameString, 1, LEN(@NameString)-3) + 'MD'
if @NameString LIKE '%[ ,.]C H A[,. ]%' OR @NameString LIKE '%[ ,.]C H A'
begin
-- Cut the existing out and replace it with 'CHA'
SET @NameString =
SUBSTRING(@NameString, 1, PATINDEX('%[ ,.]C H A%', @NameString))
+ 'CHA'
+ SUBSTRING(@NameString, PATINDEX('%[ ,.]C H A%', @NameString) + 6, LEN(@NameString))

end

set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))

if @TempString in (
SELECT Suffix FROM @Suffixs
)
begin
if @Suffix <> @TempString
begin
if len(@Suffix) > 0
set @Suffix = @TempString + ', ' + @Suffix
else
set @Suffix = @TempString
set @LastSuffix = @Suffix
if len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
end
end
else
begin
break
end
end
end
--print 'NameString_06=[' + @NameString + '], H=[' + ISNULL(@Honorific, '') + '], F=[' + ISNULL(@FirstName, '') + '], M=[' + ISNULL(@MiddleName, '') + '], L=[' + ISNULL(@LastName, 'NULL') + '], S=[' + ISNULL(@Suffix, '') + ']'

-- Count the number or words in the @NameString
SET @WordCount = dbo.GetWordCount(@NameString, NULL)
--print 'Word count=' + cast(@WordCount AS VARCHAR)
if @LastName is null OR LEN(@LastName) = 0
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 for a three part last name (before the two part)
set @ThreePart = 'N'

if @WordCount >= 4 -- 4 because I'm assuming a first name
begin
-- Extract the last & 2nd to last words
set @SaveNameString = @NameString
set @Part2 = ltrim(rtrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))))
set @NameString = substring(@NameString, 1, LEN(@NameString) - LEN(@Part2) - 1 )
set @Part1 = ltrim(rtrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))))
set @NameString = substring(@NameString, 1, LEN(@NameString) - LEN(@Part1) - 1 )
set @TempString = @Part1 + ' ' + @Part2
if @TempString in ( SELECT ThreePart FROM @ThreeParts )
begin
set @LastName = @TempString + ' ' + @LastName
set @ThreePart = 'Y'
end
else
begin
-- No match, reset the @NameString
set @NameString = @SaveNameString
end
end

if @WordCount > 2 and @ThreePart = 'N'
begin
--Check to see if the last name has two parts
set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if @TempString in ( SELECT TwoPart FROM @TwoParts )
begin
set @LastName = @TempString + ' ' + @LastName
set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
end
end
end
--print 'NameString_07=[' + @NameString + '], H=[' + ISNULL(@Honorific, '') + '], F=[' + ISNULL(@FirstName, '') + '], M=[' + ISNULL(@MiddleName, '') + '], L=[' + ISNULL(@LastName, '') + '], S=[' + ISNULL(@Suffix, '') + ']'

-- Strip any leading stray -' characters from the LastName
if len(@LastName) > 0
while ( @LastName LIKE '[*--'']%' ESCAPE '-' )
SET @LastName = RTRIM(SUBSTRING(@LastName, 2, LEN(@LastName)-1))

-- Strip any trailing stray -' characters from the LastName
if len(@LastName) > 0
while ( @LastName LIKE '%[*--'']' ESCAPE '-' )
SET @LastName = RTRIM(SUBSTRING(@LastName, 1, LEN(@LastName)-1))

--Get FirstName and strip it out of the string
set @FirstName = ltrim(rtrim(left(@NameString, charindex(' ', @NameString + ' '))))
-- Strip any leading stray -' characters from the FirstName
if len(@FirstName) > 0
while ( @FirstName LIKE '[*--'']%' ESCAPE '-' )
SET @FirstName = RTRIM(SUBSTRING(@FirstName, 2, LEN(@FirstName)))

-- Strip any trailing stray -' characters from the FirstName
if len(@FirstName) > 0
while ( @FirstName LIKE '%[*--'']' ESCAPE '-' )
SET @FirstName = RTRIM(SUBSTRING(@FirstName, 1, LEN(@FirstName)-1))

set @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))
--print 'NameString_08=[' + @NameString + '], H=[' + ISNULL(@Honorific, '') + '], F=[' + ISNULL(@FirstName, '') + '], M=[' + ISNULL(@MiddleName, '') + '], L=[' + ISNULL(@LastName, '') + '], S=[' + ISNULL(@Suffix, '') + ']'

--Anything remaining is MiddleName
set @MiddleName = LTRIM(RTRIM(@NameString))
-- Strip any leading stray -' characters from the MiddleName
if len(@MiddleName) > 0
while ( @MiddleName LIKE '[ *--'']%' ESCAPE '-' )
SET @MiddleName = RTRIM(SUBSTRING(@MiddleName, 2, LEN(@MiddleName)-1))
-- Strip any trailing stray -' characters from the MiddleName
if len(@MiddleName) > 0
while ( @MiddleName LIKE '%[ *--'']' ESCAPE '-' )
SET @MiddleName = RTRIM(SUBSTRING(@MiddleName, 1, LEN(@MiddleName)-1))

-- Return '', not NULL
if @Honorific IS NULL set @Honorific = ''
if @FirstName IS NULL set @FirstName = ''
if @MiddleName IS NULL set @MiddleName = ''
if @LastName IS NULL set @LastName = ''
if @Suffix IS NULL set @Suffix = ''

/*
--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
*/
--print 'NameString_09=[' + @NameString + '], H=[' + ISNULL(@Honorific, '') + '], F=[' + ISNULL(@FirstName, '') + '], M=[' + ISNULL(@MiddleName, '') + '], L=[' + ISNULL(@LastName, '') + '], S=[' + ISNULL(@Suffix, '') + ']'

Return --@TempString
end
go

SET NOCOUNT ON
DECLARE @idx INT
DECLARE @UserNum INT
DECLARE @NameIn varchar(100)
DECLARE @H varchar(30)
DECLARE @F varchar(30)
DECLARE @M varchar(30)
DECLARE @L varchar(30)
DECLARE @S varchar(30)
DECLARE @SmallRun char(1)

SET @SmallRun = 'Y'
SET @SmallRun = 'N'

IF @SmallRun = 'Y'
BEGIN
DECLARE @Users TABLE (
Name varchar(100)
)

SET @idx = 0
INSERT INTO @Users (Name) Values('First last c/o Company & Co.')
INSERT INTO @Users (Name) Values('Company inc. c/o First M Last')
INSERT INTO @Users (Name) Values('Mr. First Last, Jr')
INSERT INTO @Users (Name) Values('A F First Last, PhD.')
INSERT INTO @Users (Name) Values('First M Last, Ph.D.')
INSERT INTO @Users (Name) Values('First Middle Last, PhD')
INSERT INTO @Users (Name) Values('Sir. First Last, III, Esq, PhD')
INSERT INTO @Users (Name) Values('Mr & Mrs First last, III, Esq, PhD')
INSERT INTO @Users (Name) Values('Reverend Gregory Robert Von Finzer Junior')
INSERT INTO @Users (Name) Values('First de la ThreePartLast')
INSERT INTO @Users (Name) Values('De la Last, First')
INSERT INTO @Users (Name) Values('Von Last, First')
INSERT INTO @Users (Name) Values('First von Last')
INSERT INTO @Users (Name) Values('Last, First First')
INSERT INTO @Users (Name) Values('Last last last, First')
INSERT INTO @Users (Name) Values('Last, First M')
INSERT INTO @Users (Name) Values('Last last, First')
INSERT INTO @Users (Name) Values('First Last, MD')

DECLARE cNames CURSOR FOR
SELECT Name
FROM @Users
WHERE Name IS NOT NULL
AND LEN(Name) > 1
--AND Name LIKE '%(%)%'
OPEN cNames
FETCH cNames INTO @NameIn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @idx = @idx + 1
IF LEN(LTRIM(RTRIM(ISNULL(@NameIn, '')))) = 0 CONTINUE
SELECT @H = '', @F = '', @L = '', @S = ''

EXEC NameParser @NameIn
, @Honorific = @H OUTPUT
, @FirstName = @F OUTPUT
, @MiddleName = @M OUTPUT
, @LastName = @L OUTPUT
, @Suffix = @S OUTPUT

PRINT CAST(@idx AS CHAR(6)) + ' - In=[' + @NameIn + '], H=[' + ISNULL(@H, 'NULL') + '], F=[' + ISNULL(@F, 'NULL') + '], M=[' + ISNULL(@M, 'NULL') + '], L=[' + ISNULL(@L, 'NULL') + '], S=[' + ISNULL(@S, 'NULL') + ']'
--SELECT @NameIn Name, ISNULL(@H, '') Honorific, ISNULL(@F, '') First, ISNULL(@M, '') Middle, ISNULL(@L, '') Last, ISNULL(@S, '') Suffix

FETCH cNames INTO @NameIn
END
CLOSE cNames
DEALLOCATE cNames
END
ELSE
BEGIN
SET @idx = 0

DECLARE cNames CURSOR FOR
SELECT UserNum, Name
FROM Users
WHERE Name IS NOT NULL
AND LEN(Name) > 1
--AND Name LIKE '%(%)%'
OPEN cNames
FETCH cNames INTO @UserNum, @NameIn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @idx = @idx + 1
IF LEN(LTRIM(RTRIM(ISNULL(@NameIn, '')))) = 0 CONTINUE
SELECT @H = '', @F = '', @L = '', @S = ''

EXEC NameParser @NameIn
, @Honorific = @H OUTPUT
, @FirstName = @F OUTPUT
, @MiddleName = @M OUTPUT
, @LastName = @L OUTPUT
, @Suffix = @S OUTPUT

PRINT CAST(@idx AS CHAR(6)) + ' - In=[' + @NameIn + '], H=[' + ISNULL(@H, 'NULL') + '], F=[' + ISNULL(@F, 'NULL') + '], M=[' + ISNULL(@M, 'NULL') + '], L=[' + ISNULL(@L, 'NULL') + '], S=[' + ISNULL(@S, 'NULL') + ']'
--SELECT @NameIn Name, ISNULL(@H, '') Honorific, ISNULL(@F, '') First, ISNULL(@M, '') Middle, ISNULL(@L, '') Last, ISNULL(@S, '') Suffix

UPDATE Users
SET Honorific = @H, FirstName = @F, MiddleName = @M, LastName = @L, Suffix = @S
WHERE CURRENT OF cNames

FETCH cNames INTO @UserNum, @NameIn
END
CLOSE cNames
DEALLOCATE cNames
END
return
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/27/2007 :  10:34:56  Show Profile  Reply with Quote
Not sure which is more efficient....my method using CASE statements or your method using multiple inserts to table variables. Maybe someone else could comment on this.
Someday I'll get around to writing this as a CLR.

e4 d5 xd5 Nf6
Go to Top of Page

efelito
Constraint Violating Yak Guru

USA
478 Posts

Posted - 12/28/2007 :  16:30:26  Show Profile  Visit efelito's Homepage  Reply with Quote
Thanks for the post. I owe you a few hours of mind numbing string manipulation coding time. I was going to go make a CLR function until I found this script. If I get time later I'll code that up and we can compare the performace of all three.

Jeff Banschbach, MCDBA
Go to Top of Page

efelito
Constraint Violating Yak Guru

USA
478 Posts

Posted - 12/28/2007 :  17:29:46  Show Profile  Visit efelito's Homepage  Reply with Quote
FYI... there are a few name formats in our system that are breaking the function. I've added a fix to my version for the following case.

"Last, Suffix., First"

***added this after the code that checks for the suffix on the right side of the string

if @Suffix is null
begin
--Get Suffix from left sideand strip it out of the string
set @TempString = rtrim(left(@NameString, charindex(' ', @NameString)))
if @TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString
if len(@Suffix) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
end


Another example I found that is not parsing correctly is "Last Suffix, First". I have not coded a fix for this yet.

Jeff Banschbach, MCDBA
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/04/2009 :  15:42:09  Show Profile  Reply with Quote
Latest version of this code can be found on my public dropbox folder:
http://dl.dropbox.com/u/2507186/Functions/FormatName.sql

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

pianocomposer
Starting Member

1 Posts

Posted - 03/19/2010 :  11:43:51  Show Profile  Reply with Quote
I think what you have here is a good start. For medical purposes, it doesn't work and I couldn't figure out how to modify it to make it work. Does not work for: (1) Ashraf Ahmed M.D., (2) Jose Crisanto Del Rosario M.D., (3) Max Baier P.A.-C., (4) John De Martini, RN., (5) Alfred Sanford Burnham, IV, M.D.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/21/2010 :  18:22:00  Show Profile  Reply with Quote
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.
________________________________________________
Go to Top of Page

rhusky
Starting Member

1 Posts

Posted - 04/20/2010 :  12:10:58  Show Profile  Reply with Quote
This function looks great. I'm new to SQL and my question is how do i test this function?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/21/2010 :  15:59:22  Show Profile  Reply with Quote
There is syntax example at the top of my code, in the comment section.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 04/26/2010 :  15:23:22  Show Profile  Reply with Quote
The list is going to grow indefinitely.


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.
________________________________________________

Go to Top of Page

briceeric
Starting Member

3 Posts

Posted - 05/07/2011 :  22:38:16  Show Profile  Reply with Quote
For an alternative solution that is CLR based you may want to check out the free SqlName component from Ambient Concepts at: http://ambientconcepts.com/sqlname
It works with a much larger set of titles and suffixes, plus offers excellent performance.
Go to Top of Page

iflor1
Starting Member

1 Posts

Posted - 01/20/2013 :  16:16:12  Show Profile  Reply with Quote
Doesnt seem to be able to parse following name due to suffix

Pamela J Todd Battle, LAC
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.45 seconds. Powered By: Snitz Forums 2000