SQL Query: returning if column contains substring

Question

I have two tables tableA and tableB.

I want to write a query where it displays name and id if the string value of tableA's id is a substring of tableB's name. I tried the like operator but can't seem to get it to work.

   tableA
id   |
bob
cat
sky
   tableB
name   |
bobby
catlyn
bret

answer table
id     |name   | 
bob    bobby
cat    catlyn 

Answer 1

You can join on a like condition:

select a.id, b.name
from tablea a
inner join tableb b on b.name like '%' || a.id || '%'

You did not tell which database you are running, so this uses standard string concatenation operator ||; some databases use something else (eg: MySQL and SQLServer have function concat()).