On Thu, 26 May 2005 06:48:49 UTC
fr**********@gmail.com wrote:
I'm assuming that the columns are CHAR columns, so I've included RTRIM.
CREATE TABLE a (name CHAR(50));
CREATE TABLE b (first CHAR(20), last CHAR(20));
INSERT INTO a VALUES('John Q. Public');
INSERT INTO b VALUES('John', 'Public');
SELECT a.name, 'MATCHES', b.first, b.last
FROM a,b
WHERE LOCATE(RTRIM(b.first||' '), a.name) = 1
AND LOCATE(RTRIM(' '||b.last), a.name) = LENGTH(RTRIM(a.name)) -
LENGTH(RTRIM(b.last))
;
NAME 2 FIRST
LAST
-------------------------------------------------- -------
-----------------------------------------------------------------------
John Q. Public MATCHES John
Public
I'll serve as a defense witness if you do decide to strangle that
original designer.
Thanks, Fred. Needless to say, I've been away from SQL too long - I
really could use a "for Dummies" cheat sheet. There used to be a good
one on line but I've lost the url so that's no help. Now, if I can
just finagle enough info out of the rest of the tables to
differentiate 130 (out of 25k) duplicate A.name and 1470 (of 42k)
duplicate B.first/last entries I may get this usable - but I see some
manual entries in my immediate future. That's what happens when you
volunteer, I guess.
--
Will Honea