How to like two columns in one SQL statement

Question

How to write this SQL?

Table A Column aa
-----
jack
jim
alan

Table B Column bb
-----
jacky
jimmy
william

The output is:

-----
jack jacky
jim jimmy

Because aa's value is bb's substring.

Answer 1

Select aa, bb
from a, b
where a.aa like '%' + b.bb + '%'
   OR b.bb like '%' + a.aa + '%'

for mysql you need use concat('%', field, '%')

for oracle you need use '||' insteaf of '+'

Answer 2

You can construct a pattern from the substring:

select a.aa, b.bb
from TableA a
inner join TableB b on b.bb like '%' + a.aa + '%'