Exam pre-processing mis-handles the candidate names in some instances

[Ghislain Hachey]

Nothing seemingly wrong with the following student (no spaces or anything like that).

Yet once loaded looks like this.

It is not the only one. There are many (dozens per exams). The most common thing I have observed at a glance is the first name usually ends with “le” or “la” but not always.

‌

[Brian Lewis]

Two version of fnParseName existed - common.fnParseName and dbo.fnParseName. dbo version is obselete, and did not have a fix applied to the other one. dbo.fnParseName is removed and replaced everywhere by common.fnParseName

[Brian Lewis]

This SQL fragment can reapply the name from the xml row data on ExamCandidates:

UPDATE ExamCandidates
SET excGiven = N.FirstName
, excFamilyName = N.LastName
from ExamCandidates
CROSS APPLY common.fnparseName(excData.value('(//row/@FullName)[1]', 'nvarchar(50)')) N
where excGiven is null and N.FirstName is not null

This version parses incorrectly concatenated names, assuming each part begins with an uppercase letter:

‌

begin transaction

select excData.value('(//row/@FullName)[1]', 'nvarchar(50)')
, N.FirstName, N.MiddleName, N.LastName
, excGiven, excMiddleNames, excFamilyName , exID
from ExamCandidates
CROSS APPLY common.fnparseName(common.spaceName(excData.value('(//row/@FullName)[1]', 'nvarchar(50)'))) N
where excGiven = N.FirstName
AND excFamilyName = N.LastName
AND excMiddleNAmes is null AND N.MiddleName is not null

UPDATE ExamCandidates
SET excGiven = N.FirstName
, excMiddleNames = N.MiddleName
, excFamilyName = N.LastName
from ExamCandidates
CROSS APPLY common.fnparseName(excData.value('(//row/@FullName)[1]', 'nvarchar(50)')) N
where excGiven = N.FirstName
AND excFamilyName = N.LastName
AND excMiddleNAmes is null AND N.MiddleName is not null

UPDATE ExamCandidates
SET excGiven = N.FirstName
, excMiddleNames = N.MiddleName
, excFamilyName = N.LastName
from ExamCandidates
CROSS APPLY common.fnparseName(common.spaceName(excData.value('(//row/@FullName)[1]', 'nvarchar(50)'))) N
where excGiven = N.FirstName
AND excFamilyName = N.LastName
AND excMiddleNAmes is null AND N.MiddleName is not null


select excData.value('(//row/@FullName)[1]', 'nvarchar(50)')
, N.FirstName, N.MiddleName, N.LastName
, excGiven, excMiddleNames, excFamilyName , exID
from ExamCandidates
CROSS APPLY common.fnparseName(common.spaceName(excData.value('(//row/@FullName)[1]', 'nvarchar(50)'))) N
where excGiven = N.FirstName
AND excFamilyName = N.LastName
AND excMiddleNAmes is null AND N.MiddleName is not null
rollback

‌