467,168 Members | 1,027 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,168 developers. It's quick & easy.

How to join those 2 char columns?

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
  • viewed: 1166
Share:
4 Replies
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
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
Hmm.. forget this. It fails if col1 shows up twice in col2.

Mehmet's answer is very good.

B.

Apr 27 '06 #4
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.

Similar topics

6 posts views Thread by Xenophobe | last post: by
7 posts views Thread by stabbert | last post: by
2 posts views Thread by Doug Crowson | last post: by
6 posts views Thread by davegb | last post: by
2 posts views Thread by Bennett Haselton | last post: by
6 posts views Thread by PW | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.