SEO

#1 (**permalink**) 02-27-2008, 03:50 AM

Two tables - table A has a column of full names (First MI Last), table
B has 2 columns (First, Last) but no middle (and yes, I'd like to
strangle the idiot who designed it!). Is there a syntax that will
allow a query on something similar to:

A.name like (B.first || % || B.last)?

Everything I try seems to wind up with errors where on operand is not
a string.

--
Will Honea

#2 (**permalink**) 02-27-2008, 03:50 AM

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.

Fred
frsconsulting.com

#3 (**permalink**) 02-27-2008, 03:50 AM

On Thu, 26 May 2005 06:48:49 UTC fred.sobotka@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

#4 (**permalink**) 02-27-2008, 03:51 AM

Hi Will,

I'm a big fan of a free book by Graeme Birchall called the DB2 UDB
Cookbook. Mr. Birchall has kept the book up to date with the latest
version of DB2, and it's a pretty good read.

http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

Good luck,

Fred
frsconsulting.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Query syntax