How to compare two columns in DB2 -Please HELP!

[James You]

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

[Serge Rielau]

James You wrote:

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?

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

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

[Tonkuma]

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

[James You]

Thank you to both of you! Serge and Tonkuma
James