How to join those 2 char columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shenanwei@gmail.com - 27 Apr 2006 18:23 GMT

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 ?

Reply to this Message

Mehmet Baserdem - 27 Apr 2006 18:41 GMT

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

Reply to this Message

Brian Tkatch - 27 Apr 2006 20:53 GMT

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.

Reply to this Message

Brian Tkatch - 27 Apr 2006 20:58 GMT

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

Mehmet's answer is very good.

B.

Reply to this Message

Tonkuma - 29 Apr 2006 05:27 GMT

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

Reply to this Message