How to compare two columns in DB2 -Please HELP!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James You - 08 Jan 2006 07:49 GMT

Hello,

I need to compare two columns in two tables and to find the rows with
partially matched result. For example, 'Jim' is deemed as matching
'Jimmy' and 'Jeff ' as matching 'Jeffery'. In Microsoft jet
(or in sql server similarly) this can be done by using:

"SELECT tableA.column1, tableB.column1 FROM tableA INNER JOIN tableB
ON tableA.column1 LIKE tableB.column1 & %".

But in db2, 'LIKE' operator can not be used on column. Do you have
any idea how to do this in DB2?

There must be a way to do it in DB2 if it can be done elsewhere easily.
What do you DB2 experts think?

Any help is appreciated,

James

Reply to this Message

Tonkuma - 09 Jan 2006 09:08 GMT

SELECT tableA.column1, tableB.column1 FROM tableA INNER JOIN tableB
ON LOCATE(tableB.column1, tableA.column1) = 1;

Reply to this Message

Serge Rielau - 09 Jan 2006 09:26 GMT

> Hello,
>
[quoted text clipped - 11 lines]
> There must be a way to do it in DB2 if it can be done elsewhere easily.
> What do you DB2 experts think?

SELECT tableA.column1, tableB.column1 FROM tableA INNER JOIN tableB
ON SUBSTR(tableA.column1, 1, LENGTH(tableB.column1)) = tableB.column1
AND tableA.column1 >= tableB.column1

I added the >= predicate for performance only. It may give you a start
key (or not).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

James You - 10 Jan 2006 00:14 GMT

Thank you to both of you! Serge and Tonkuma
James

Reply to this Message