Make sure the value of one column contains the value of another

Question

I have a table with java package names:

|id|pkg| |1 |some.long.package.path| |2 |com.admob.bla| 

and a table containing partial package names form known advertising frames.

 |id|path_fragment| |1 |.admob | 

How can I get packages whose value for pkg contains any of ad_frameworks.path_fragment?

I just can't find substring validation. All that I find are people who use as a comparison with some string literal, but never to compare columns.

NOTE. I am using MySQL.

Answer 1

I think you could make an inner join with a similar

 select p.id, p.pkg from package p inner join ad_frameworks adf on p.pkg like CONCAT('%', adf.path_fragment, '%') --group by p.id, p.pkg 

or as you indicated

 select * from package p inner join ad_frameworks adf on LOCATE(adf.fragment, p.pkg) > 0 

or

 select * from package p inner join ad_frameworks adf on INSTR( p.pkg,adf.fragment) > 0 

or

 select * from package p inner join ad_frameworks adf on POSITION(adf.fragment IN p.pkg) > 0 

or

 select * from package p inner join ad_frameworks adf on REPLACE(p.pkg, adf.fragemnt, '') <> p.pkg 

Well, you have few options;)

SqlFiddle

Answer 2

Try it,

 SELECT a.*, b.* FROM package a, ad_frameworks b WHERE a.pkg LIKE CONCAT('%',b.path_fragment,'%') 

SQLFiddle Demo

Answer 3

Try something like this:

 SELECT distinct a.pkg FROM TAB_1 as a INNER JOIN TAB_2 as b ON a.pkg LIKE CONCAT('%', b.path_fragment, '%') 

Answer 4

You can do something like this:

 select p.id, pkg from packages p inner join frameworks f on p.pkg like concat('%',f.path_fragment,'%')