Pfthb

I can't get a join to work. I have extra whitespace and trailing content so I thought replace, trim, like, and concat (for the wildcard) would work but it isn't.

Here's sample data:

create table table1 (name varchar(200));
create table table2 (name varchar(200));
insert into table1 values
('A test: A Value'),
('A test: Another value');
insert into table2 values
('A Value: extra content'),
('Another Value: More content');

I expected row 1 of table1 to match row 1 of table2, and the same for row 2. These should match because the content after A test: in table 1 matches the leading text of table 2.

My attempt was:

select *
from table1 as t1
join table2 as t2
on trim(replace(replace(t1.name, 'A test:', ''), ' ', ' ')) 
like concat(trim(replace(t2.name, ' ', ' ')), '%') 

This returns no matches though,

http://sqlfiddle.com/#!9/940742/4

The reason it doesn't work is that you are trying to join on a condition where the first part is shorter than the second, for example:

on 'A Value' like 'A Value: extra content%'

so in order to get it to work you need to switch the arguments so that the condition becomes:

on 'A Value: extra content' like 'A Value%'

I believe you should change the join clause so that the % gets concatenated with the t1.name, not the t2.name.

This should work:

select * 
from table1 as t1 
join table2 as t2
on trim(replace(t2.name, ' ', ' ')) 
like concat(trim(replace(replace(t1.name, 'A test:', ''), ' ', ' ')), '%') ;

See http://sqlfiddle.com/#!9/940742/23