I have a table with java package names:
|id|pkg|
|1 |some.long.package.path|
|2 |com.admob.bla|
and a table that contains partial packages names form known advertisement frameworks.
|id|path_fragment|
|1 |.admob |
How can I retrieve packages whose value for pkg contains any of the ad_frameworks.path_fragment?
I just can't find the substring check. All I find is people using like to compare to some string literal but never for comparing columns.
NOTE: I am using MySQL.
Combining INDEX and MATCH to Lookup Value in Column and Return Value of Another Column. Apart from that, you can use nested INDEX and MATCH formulas to lookup for a value in a column and get the result of another column in the dataset.
think you could do an inner join with a like
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 pointed
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've got few choices ;)
SqlFiddle
Try this,
SELECT a.*, b.*
FROM package a, ad_frameworks b
WHERE a.pkg LIKE CONCAT('%',b.path_fragment,'%')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With