IF EXISTS(SELECT name FROM sysobjects WHERE name = 'ParseAccountName' AND type = 'P')
DROP PROCEDURE ParseAccountName
GO
CREATE PROCEDURE ParseAccountName
@CustomerType VARCHAR(2),
@CommaDeleminited TINYINT,
@LastNameIsFirst TINYINT
AS
DECLARE @CustomerTypeId INT
SET NOCOUNT ON
BEGIN TRANSACTION
SET @CustomerTypeId = (SELECT CustomerTypeId
FROM CustomerTypes
WHERE UPPER(LTRIM(RTRIM(CustomerType))) = UPPER(LTRIM(RTRIM(@CustomerType)))
AND Deleted = 0)
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while getting the CustomerTypeId.', 16, 1)
RETURN(99)
END
IF @CustomerTypeId IS NULL
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('The specified customer type was not found.', 16, 1)
RETURN(99)
END
/* Be sure that the spaces in the last name are trimmed out. */
UPDATE Accounts
SET LastName = LTRIM(RTRIM(ISNULL(LastName, '')))
WHERE Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while trimming the last name.', 16, 1)
RETURN(99)
END
/* Temporarily remove the spaces from around any "ands" for parsing purposes. */
UPDATE Accounts
SET LastName = REPLACE(LastName, ' &', '~&')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX(' &', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the leading spaces from around the ampersands.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '& ', '&~')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('& ', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the trailing spaces from around the ampersands.', 16, 1)
RETURN(99)
END
/* Note that for the word AND, we have to have spaces on both sides, since it could be a valid name otherwise,
such as Anderson or Outland. */
UPDATE Accounts
SET LastName = REPLACE(LastName, ' AND ', '~and~')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX(' AND ', UPPER(LastName)) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the spaces from around the AND''s.', 16, 1)
RETURN(99)
END
/* Recapitalize the second name where the conversion messed it up for names like Thompson James/Kristy. */
UPDATE Accounts
SET LastName = LEFT(LastName, CHARINDEX('/', LastName)) + UPPER(SUBSTRING(LastName, CHARINDEX('/', LastName) + 1, 1)) + SUBSTRING(LastName, CHARINDEX('/', LastName) + 2, LEN(LastName) - CHARINDEX('/', LastName) + 2)
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('/', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while capitalizing the second first name separated by a /.', 16, 1)
RETURN(99)
END
/* Recapitalize the second name where the conversion messed it up for names like Thompson James\Kristy. */
UPDATE Accounts
SET LastName = LEFT(LastName, CHARINDEX('\', LastName)) + UPPER(SUBSTRING(LastName, CHARINDEX('\', LastName) + 1, 1)) + SUBSTRING(LastName, CHARINDEX('\', LastName) + 2, LEN(LastName) - CHARINDEX('\', LastName) + 2)
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('\', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while capitalizing the second first name separated by a \.', 16, 1)
RETURN(99)
END
/* Temporarily remove any spaces around any slashes for parsing purposes.
i.e. For names like Thompson James / Kristy or Thompson James \ Kristy. */
UPDATE Accounts
SET LastName = REPLACE(LastName, ' /', '~/')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX(' /', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the leading spaces from around the /''s.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '/ ', '/~')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('/ ', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the trailing spaces from around the /''s.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, ' \', '~\')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX(' \', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the leading spaces from around the \''s.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '\ ', '\~')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('\ ', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while removing the trailing spaces from around the \''s.', 16, 1)
RETURN(99)
END
IF @LastNameIsFirst = 1
BEGIN
/* Parse out the salutations. */
UPDATE Accounts
SET Salutation = RIGHT(LastName, 10),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 10))
WHERE RIGHT(UPPER(LastName), 10) = 'MR.~&~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. & MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 8),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MR~&~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR & MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 10),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 10))
WHERE RIGHT(UPPER(LastName), 10) = 'MRS.~&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. & MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 8),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MRS~&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS & MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Mrs.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MR.&~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR.& MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Mrs',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MR&~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR& MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs. & Mr.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MRS.&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS.& MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs & Mr',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MRS&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS& MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Mrs.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MR.~&MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. &MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Mrs',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MR~&MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR &MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs. & Mr.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MRS.~&MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. &MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs & Mr',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MRS~&MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS &MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 9),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MR.~&~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. & MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 7),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MR~&~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR & MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 9),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MS.~&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. & MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 7),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 7))
WHERE RIGHT(UPPER(LastName), 7) = 'MS~&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS & MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Ms.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MR.&~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR.& MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Ms',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 6))
WHERE RIGHT(UPPER(LastName), 6) = 'MR&~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR& MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms. & Mr.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MS.&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS.& MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms & Mr',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 6))
WHERE RIGHT(UPPER(LastName), 6) = 'MS&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS& MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Ms.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MR.~&MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. &MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Ms',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 6))
WHERE RIGHT(UPPER(LastName), 6) = 'MR~&MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR &MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms. & Mr.',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 8))
WHERE RIGHT(UPPER(LastName), 8) = 'MS.~&MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. &MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms & Mr',
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 6))
WHERE RIGHT(UPPER(LastName), 6) = 'MS~&MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS &MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 12),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 12))
WHERE RIGHT(UPPER(LastName), 12) = 'MR.~and~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. and MRS. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 10),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 10))
WHERE RIGHT(UPPER(LastName), 10) = 'MR~and~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR and MRS Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 12),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 12))
WHERE RIGHT(UPPER(LastName), 12) = 'MRS.~and~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. and MR. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 10),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 10))
WHERE RIGHT(UPPER(LastName), 10) = 'MRS~and~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS and MR Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 11),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 11))
WHERE RIGHT(UPPER(LastName), 11) = 'MR.~and~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. and MS. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 9),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MR~and~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR and MS Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 11),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 11))
WHERE RIGHT(UPPER(LastName), 11) = 'MS.~and~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. and MR. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 9),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 9))
WHERE RIGHT(UPPER(LastName), 9) = 'MS~and~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS and MR Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'DR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the DR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'DR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the DR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 4),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 4))
WHERE RIGHT(UPPER(LastName), 4) = 'MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 4) = 'MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = RIGHT(LastName, 4),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 4))
WHERE RIGHT(UPPER(LastName), 4) = 'MISS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MISS Salutation.', 16, 1)
RETURN(99)
END
/* Parse out the suffixes. */
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'JR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the JR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'JR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the JR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'SR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the SR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'SR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the SR Salutation.', 16, 1)
RETURN(99)
END
/* Now that we have the salutations and suffixes parsed out, set the first and middle name fields. */
IF @CommaDeleminited > 0
BEGIN
/* Only Set the middle name if there is a comma in the last name field */
UPDATE Accounts
SET MiddleName = LTRIM(RIGHT(LastName, CHARINDEX(' ', REVERSE(LastName), 1)-1)),
LastName = LEFT(LastName, LEN(LastName) - CHARINDEX(' ', REVERSE(LastName), 1))
WHERE CHARINDEX(' ', REVERSE(LastName), 1) > 0
AND CHARINDEX(',', LastName, 1) > 0
AND CHARINDEX('&', LastName, 1) = 0
AND CHARINDEX(',', LastName, 1) < CHARINDEX(' ', LastName, 1)
AND LTRIM(RTRIM(ISNULL(MiddleName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the Middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = LTRIM(RIGHT(LastName, CHARINDEX(',', REVERSE(LastName), 1)-1)),
LastName = LEFT(LastName, LEN(LastName) - CHARINDEX(',', REVERSE(LastName), 1))
WHERE CHARINDEX(',', REVERSE(LastName), 1) > 0
AND LTRIM(RTRIM(ISNULL(FirstName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = MiddleName,
MiddleName = NULL
WHERE CHARINDEX(',', REVERSE(LastName), 1) <= 0
AND LTRIM(RTRIM(ISNULL(FirstName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name to the middle name, where necessary.', 16, 1)
RETURN(99)
END
END
ELSE
BEGIN
UPDATE Accounts
SET MiddleName = LTRIM(RIGHT(LastName, CHARINDEX(' ', REVERSE(LastName), 1))),
LastName = LEFT(LastName, LEN(LastName) - CHARINDEX(' ', REVERSE(LastName), 1))
WHERE CHARINDEX(' ', REVERSE(LastName), 1) > 0
AND LTRIM(RTRIM(ISNULL(MiddleName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = LTRIM(RIGHT(LastName, CHARINDEX(' ', REVERSE(LastName), 1))),
LastName = LEFT(LastName, LEN(LastName) - CHARINDEX(' ', REVERSE(LastName), 1))
WHERE CHARINDEX(' ', REVERSE(LastName), 1) > 0
AND LTRIM(RTRIM(ISNULL(FirstName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name.', 16, 1)
RETURN(99)
END
/* If there isn't another space then the name put in the middle name is actually the first name. */
UPDATE Accounts
SET FirstName = MiddleName,
MiddleName = NULL
WHERE CHARINDEX(' ', REVERSE(LastName), 1) <= 0
AND LTRIM(RTRIM(ISNULL(FirstName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name to the middle name, where necessary.', 16, 1)
RETURN(99)
END
END
END
ELSE /* First name is first in the last name field. */
BEGIN
/* Parse out the salutations. */
UPDATE Accounts
SET Salutation = LEFT(LastName, 10),
LastName = LTRIM(SUBSTRING(LastName, 11, LEN(LastName) - 10))
WHERE LEFT(UPPER(LastName), 10) = 'MR.~&~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. & MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 8),
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MR~&~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR & MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 10),
LastName = LTRIM(SUBSTRING(LastName, 11, LEN(LastName) - 10))
WHERE LEFT(UPPER(LastName), 10) = 'MRS.~&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. & MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 8),
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MRS~&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS & MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Mrs.',
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MR.&~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR.& MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Mrs',
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MR&~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR& MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs. & Mr.',
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MRS.&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS.& MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs & Mr',
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MRS&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS& MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Mrs.',
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MR.~&MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. &MRS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Mrs',
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MR~&MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR &MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs. & Mr.',
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MRS.~&MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. &MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mrs & Mr',
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MRS~&MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS &MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 9),
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MR.~&~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. & MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 7),
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MR~&~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR & MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 9),
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MS.~&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. & MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 7),
LastName = LTRIM(SUBSTRING(LastName, 8, LEN(LastName) - 7))
WHERE LEFT(UPPER(LastName), 7) = 'MS~&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS & MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Ms.',
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MR.&~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR.& MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Ms',
LastName = LTRIM(SUBSTRING(LastName, 7, LEN(LastName) - 6))
WHERE LEFT(UPPER(LastName), 6) = 'MR&~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR& MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms. & Mr.',
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MS.&~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS.& MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms & Mr',
LastName = LTRIM(SUBSTRING(LastName, 7, LEN(LastName) - 6))
WHERE LEFT(UPPER(LastName), 6) = 'MS&~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS& MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr. & Ms.',
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MR.~&MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. &MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Mr & Ms',
LastName = LTRIM(SUBSTRING(LastName, 7, LEN(LastName) - 6))
WHERE LEFT(UPPER(LastName), 6) = 'MR~&MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR &MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms. & Mr.',
LastName = LTRIM(SUBSTRING(LastName, 9, LEN(LastName) - 8))
WHERE LEFT(UPPER(LastName), 8) = 'MS.~&MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. &MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = 'Ms & Mr',
LastName = LTRIM(SUBSTRING(LastName, 7, LEN(LastName) - 6))
WHERE LEFT(UPPER(LastName), 6) = 'MS~&MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS &MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 12),
LastName = LTRIM(SUBSTRING(LastName, 13, LEN(LastName) - 12))
WHERE LEFT(UPPER(LastName), 12) = 'MR.~and~MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. and MRS. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 10),
LastName = LTRIM(SUBSTRING(LastName, 11, LEN(LastName) - 10))
WHERE LEFT(UPPER(LastName), 10) = 'MR~and~MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR and MRS Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 12),
LastName = LTRIM(SUBSTRING(LastName, 13, LEN(LastName) - 12))
WHERE LEFT(UPPER(LastName), 12) = 'MRS.~and~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. and MR. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 10),
LastName = LTRIM(SUBSTRING(LastName, 11, LEN(LastName) - 10))
WHERE LEFT(UPPER(LastName), 10) = 'MRS~and~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS and MR Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 11),
LastName = LTRIM(SUBSTRING(LastName, 12, LEN(LastName) - 11))
WHERE LEFT(UPPER(LastName), 11) = 'MR.~and~MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. and MS. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 9),
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MR~and~MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR and MS Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 11),
LastName = LTRIM(SUBSTRING(LastName, 12, LEN(LastName) - 11))
WHERE LEFT(UPPER(LastName), 11) = 'MS.~and~MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. and MR. Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 9),
LastName = LTRIM(SUBSTRING(LastName, 10, LEN(LastName) - 9))
WHERE LEFT(UPPER(LastName), 9) = 'MS~and~MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS and MR Salutation.', 18, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 3),
LastName = LTRIM(SUBSTRING(LastName, 4, LEN(LastName) - 3))
WHERE LEFT(UPPER(LastName), 3) = 'DR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the DR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 2),
LastName = LTRIM(SUBSTRING(LastName, 3, LEN(LastName) - 2))
WHERE LEFT(UPPER(LastName), 2) = 'DR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the DR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 4),
LastName = LTRIM(SUBSTRING(LastName, 5, LEN(LastName) - 4))
WHERE LEFT(UPPER(LastName), 4) = 'MRS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS. salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 3),
LastName = LTRIM(SUBSTRING(LastName, 4, LEN(LastName) - 3))
WHERE LEFT(UPPER(LastName), 4) = 'MRS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MRS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 3),
LastName = LTRIM(SUBSTRING(LastName, 4, LEN(LastName) - 3))
WHERE LEFT(UPPER(LastName), 3) = 'MR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 2),
LastName = LTRIM(SUBSTRING(LastName, 3, LEN(LastName) - 2))
WHERE LEFT(UPPER(LastName), 2) = 'MR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 3),
LastName = LTRIM(SUBSTRING(LastName, 4, LEN(LastName) - 3))
WHERE LEFT(UPPER(LastName), 3) = 'MS.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 2),
LastName = LTRIM(SUBSTRING(LastName, 3, LEN(LastName) - 2))
WHERE LEFT(UPPER(LastName), 2) = 'MS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MS Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = LEFT(LastName, 4),
LastName = LTRIM(SUBSTRING(LastName, 5, LEN(LastName) - 4))
WHERE LEFT(UPPER(LastName), 4) = 'MISS'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the MISS Salutation.', 16, 1)
RETURN(99)
END
/* Parse out the suffixes. */
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'JR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the JR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'JR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the JR Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 3),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 3))
WHERE RIGHT(UPPER(LastName), 3) = 'SR.'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the SR. Salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET NameSuffix = RIGHT(LastName, 2),
LastName = RTRIM(LEFT(LastName, LEN(LastName) - 2))
WHERE RIGHT(UPPER(LastName), 2) = 'SR'
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while parsing the SR Salutation.', 16, 1)
RETURN(99)
END
/* Now that we have the salutations and suffixes parsed out, set the first and middle name fields. */
UPDATE Accounts
SET FirstName = RTRIM(LEFT(LastName, CHARINDEX(' ', LastName, 1))),
LastName = SUBSTRING(LastName, CHARINDEX(' ', LastName, 1) + 1, LEN(LastName) - CHARINDEX(' ', LastName, 1))
WHERE CHARINDEX(' ', LastName, 1) > 0
AND LTRIM(RTRIM(ISNULL(FirstName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = RTRIM(LEFT(LastName, CHARINDEX(' ', LastName, 1))),
LastName = SUBSTRING(LastName, CHARINDEX(' ', LastName, 1) + 1, LEN(LastName) - CHARINDEX(' ', LastName, 1))
WHERE CHARINDEX(' ', LastName, 1) > 0
AND LTRIM(RTRIM(ISNULL(MiddleName, ''))) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the middle name.', 16, 1)
RETURN(99)
END
END
/* Put the spaces back around any "ands" in the salulation. */
UPDATE Accounts
SET Salutation = REPLACE(Salutation, '~&', ' &')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~&', Salutation) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the ampersands in the salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = REPLACE(Salutation, '&~', '& ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('&~', Salutation) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the ampersands in the salutation.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET Salutation = REPLACE(UPPER(Salutation), '~AND~', ' and ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~AND~', UPPER(Salutation)) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the spaces back around the AND''s in the salutation.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any "ands" in the first name. */
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '~&', ' &')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~&', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the ampersands in the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '&~', '& ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('&~', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the ampersands in the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = REPLACE(UPPER(FirstName), '~AND~', ' and ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~AND~', UPPER(FirstName)) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the spaces back around the AND''s in the first name.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any "ands" in the middle name. */
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '~&', ' &')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~&', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the ampersands in the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '&~', '& ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('&~', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the ampersands in the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = REPLACE(UPPER(MiddleName), '~AND~', ' and ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~AND~', UPPER(MiddleName)) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the spaces back around the AND''s in the middle name.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any "ands" in the last name. */
UPDATE Accounts
SET LastName = REPLACE(LastName, '~&', ' &')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~&', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the ampersands in the last name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '&~', '& ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('&~', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the ampersands in the last name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(UPPER(LastName), '~AND~', ' and ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~AND~', UPPER(LastName)) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the spaces back around the AND''s in the last name.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any slashes in the first name. */
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '~/', ' /')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~/', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the /''s in the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '/~', '/ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('/~', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the /''s in the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '~\', ' \')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~\', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the \''s in the first name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET FirstName = REPLACE(FirstName, '\~', '\ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('\~', FirstName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the \''s in the first name.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any slashes in the middle name. */
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '~/', ' /')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~/', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the /''s in the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '/~', '/ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('/~', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the /''s in the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '~\', ' \')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~\', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the \''s in the middle name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET MiddleName = REPLACE(MiddleName, '\~', '\ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('\~', MiddleName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the \''s in the middle name.', 16, 1)
RETURN(99)
END
/* Put the spaces back around any slashes in the last name. */
UPDATE Accounts
SET LastName = REPLACE(LastName, '~/', ' /')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~/', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the /''s in the last name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '/~', '/ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('/~', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the /''s in the last name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '~\', ' \')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('~\', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the leading spaces back around the \''s in the last name.', 16, 1)
RETURN(99)
END
UPDATE Accounts
SET LastName = REPLACE(LastName, '\~', '\ ')
WHERE Accounts.CustomerTypeId = @CustomerTypeId
AND CHARINDEX('\~', LastName) > 0
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while putting the trailing spaces back around the \''s in the last name.', 16, 1)
RETURN(99)
END
/* Set the last name back to NULL where its blank. */
UPDATE Accounts
SET LastName = NULL
WHERE LTRIM(RTRIM(LastName)) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the last name to NULL where it is blank.', 16, 1)
RETURN(99)
END
/* Set the first name back to NULL where its blank. */
UPDATE Accounts
SET FirstName = NULL
WHERE LTRIM(RTRIM(FirstName)) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the first name to NULL where it is blank.', 16, 1)
RETURN(99)
END
/* Set the middle name back to NULL where its blank. */
UPDATE Accounts
SET MiddleName = NULL
WHERE LTRIM(RTRIM(MiddleName)) = ''
AND Accounts.CustomerTypeId = @CustomerTypeId
IF @@ERROR <> 0
BEGIN
SET NOCOUNT OFF
ROLLBACK TRANSACTION
RAISERROR('An error occured while setting the middle name to NULL where it is blank.', 16, 1)
RETURN(99)
END
SET NOCOUNT OFF
COMMIT TRANSACTION