CODEWITHSUNDEEP
mysqlsql
er4z0r
er4z0r

Reputation: 4891

Check if the value of one column contains the value of another

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.

Upvotes: 7

Views: 12483

Answers (4)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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

Upvotes: 18

Gabriel
Gabriel

Reputation: 31

You could do something like this:

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

Upvotes: 0

WojtusJ
WojtusJ

Reputation: 1318

Try something like that:

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

Upvotes: 0

John Woo
John Woo

Reputation: 263723

Try this,

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

SQLFiddle Demo

Upvotes: 2

Related Questions