How to like two columns in one SQL statement

Question

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

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

output is:
-----
jack jacky
jim jimmy

because aa's value is bb's substring. How to write this SQL?

Answer 1

You can try something like

DECLARE @TableA TABLE(
        AA VARCHAR(20)
)
INSERT INTO @TableA SELECT 'jack'
INSERT INTO @TableA SELECT 'jim'
INSERT INTO @TableA SELECT 'alan' 

DECLARE @TableB TABLE(
        BB VARCHAR(20)
)
INSERT INTO @TableB SELECT 'jacky'
INSERT INTO @TableB SELECT 'jimmy'
INSERT INTO @TableB SELECT 'william'

SELECT  *
FROM    @TableA A,
        @TableB B
WHERE   B.BB LIKE '%' + A.AA + '%'

Answer 2

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 3

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 + '%'

Answer 4

You can do something like this:

SELECT
       a.aa, b.bb
FROM   
       a
JOIN   
       b ON b.bb like '%' + a.aa + '%'

But you have to be very carefully because you can find multiple rows returned for the same name.