That just gives me the longest name there. This doesn't validate that the
name is actually correct and that there isn't a data entry error that needs
to be addressed manually. For example, if I have the following:
W. Scott Miller
Q. Scott Miller
The choice of which one would be used would be random with the supplied SQL.
Yet this is a "needs attention" set of records for a given SSN because how
can the first name of an individual be both Q and W? The routine should
check initials against the longest name to make sure they match.
Secondly, if we have:
Mo Norm Smith
M. Norm Smith
Which one actually gets chosen? Both first names are two charaters long,
yet one is a actual name and the other is an initial?
Is there a way to strip out punctuation before checking length?
Thanks for the reply,
Remove the words spam and killer from the e-mail to reply.
"David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
news:%23CjejA$PDHA.1612TK2MSFTNGP11.phx.gbl...style> Assuming that "most complete" means the longest string value from each
> CREATE TABLE Sometable (ssn INTEGER NOT NULL, firstname VARCHAR(20) NOT
> NULL, middlename VARCHAR(20) NOT NULL, lastname VARCHAR(20) NOT NULL,KEY(ssn,firstname,middlename,lastname,style))> VARCHAR(20) NOT NULL, PRIMARYfirstname,>
> INSERT INTO Sometable VALUES (1,'W', 'Scott', 'Miller', 'Jr.')
> INSERT INTO Sometable VALUES (1,'William', 'S', 'Miller', 'Jr.')
> INSERT INTO Sometable VALUES (1,'W.', 'S.', 'Miller', 'Jr')
> INSERT INTO Sometable VALUES (2,'John', 'Q.', 'Doe', '')
> SELECT S.ssn,
> MIN(CASE LEN(firstname) WHEN lenfirstname THEN firstname END) AS> MIN(CASE LEN(middlename) WHEN lenmiddlename THEN middlename END) AS
> MIN(CASE LEN(lastname) WHEN lenlastname THEN lastname END) AS lastname,
> MIN(CASE LEN(style) WHEN lenstyle THEN style END) AS style
> FROM Sometable AS S
> (SELECT ssn,
> MAX(LEN(firstname)) AS lenfirstname,
> MAX(LEN(middlename)) AS lenmiddlename,
> MAX(LEN(lastname)) AS lenlastname,
> MAX(LEN(style)) AS lenstyle
> FROM Sometable
> GROUP BY ssn) AS L
> ON S.ssn = L.ssn
> GROUP BY S.ssn
> David Portas
> Please reply only to the newsgroup