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,
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:', ''), ' ', ' ')), '%') ;