SQL: Join tables on substrings

I have a table A with the string-column a and a table B with the string-column b. a is a substring of b. Now I want to join the the two tables on a and b. Is this possible?

I want something like this:

Select * from A,B where A.a *"is substring of"* B.b

How can I write this in SQL (Transact-SQL)?

Answer

You can use like

select *
from A
  inner join B 
    on B.b like '%'+A.a+'%'