By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

Query syntax

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.