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

How to join those 2 char columns?

P: n/a
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
Share this Question
Share on Google+
4 Replies


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

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

P: n/a
Hmm.. forget this. It fails if col1 shows up twice in col2.

Mehmet's answer is very good.

B.

Apr 27 '06 #4

P: n/a
SELECT col1, col2
FROM table1
JOIN
table2
ON col1 = SUBSTR(col2, 1, POSSTR(col2||' ', ' ')-1)

Apr 29 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.