How to join those 2 char columns?

shenanwei

I have
create table table1 (col1 varchar(50));
insert into table1 values ('abc'), ('defg');

create table table2 (col2 varchar(50));
insert into table2 values ('abc d'), ('defg h'), ('abcd'), ('defgh');

And I need those as the result
'abc', 'abc d'
'defg', 'defg h'

select col1, col2 from table1 join table2 on ?

Apr 27 '06 #1

Mehmet Baserdem

select col1, col2 from table1, (select col2, (CASE WHEN posstr(col2,'
') > 0 then substr(col2,1,posstr(col2,' ')) else col2 end) as tempcol
from table2) as temptable where col1 = tempcol

regards

Mehmet Baserdem

Apr 27 '06 #2

Brian Tkatch

DECLARE GLOBAL TEMPORARY TABLE Table1 (Col1 VARCHAR(50))
INSERT INTO SESSION.Table1 VALUES ('abc'), ('defg')

DECLARE GLOBAL TEMPORARY TABLE Table2 (Col2 VARCHAR(50))
INSERT INTO SESSION.Table2 values ('abc d'), ('defg h'), ('abcd'),
('defgh')

SELECT Col1, Col2 FROM SESSION.Table1, SESSION.Table2 WHERE
VARCHAR(Col1 || ' ' || REPLACE(Col2, Col1 || ' ', ''), 50) = Col2

DROP TABLE SESSION.Table1
DROP TABLE SESSION.Table2
COMMIT

B.

Apr 27 '06 #3

Brian Tkatch

Hmm.. forget this. It fails if col1 shows up twice in col2.

Mehmet's answer is very good.

B.

Apr 27 '06 #4

Tonkuma

SELECT col1, col2
FROM table1
JOIN
table2
ON col1 = SUBSTR(col2, 1, POSSTR(col2||' ', ' ')-1)

Apr 29 '06 #5