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

quote:

---

Msg 207, Level 16, State 1, Line 0
Invalid column name 'Honorific'.
Msg 207, Level 16, State 1, Line 0
Invalid column name 'FirstName'.
Msg 207, Level 16, State 1, Line 0
Invalid column name 'MiddleName'.
Msg 207, Level 16, State 1, Line 0
Invalid column name 'LastName'.
Msg 207, Level 16, State 1, Line 0
Invalid column name 'Suffix'.

---

Create 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