Using JOIN statement with CONTAINS function

It's not that CONTAINS can't be used in joins.

You just can't use columns as a second parameter of CONTAINS - see MSDN - CONTAINS (Transact-SQL)

CONTAINS
( { column_name | ( column_list ) | * } 
  ,'<contains_search_condition>'     
[ , LANGUAGE language_term ]
) 

However, you can use a variable as a search condition, so you can use a cursor and then get all data you need. Here is some very rough example:

declare @Name nvarchar(max)

declare @Temp_A table(Name nvarchar(max))
declare @Temp_B table(Name nvarchar(max))

--=============================================================================================
insert into @Temp_A (Name)
select 'Test'

insert into @Temp_B (Name)
select 'aaaTestaaa'

--=============================================================================================
-- Query 1 - LIKE
--=============================================================================================
select *
from @Temp_A as A
    inner join @Temp_B as B on B.Name like '%' + A.Name + '%'

--=============================================================================================
-- Query 2 - CONTAINS
--=============================================================================================
declare table_cursor cursor local fast_forward for
    select distinct Name from @Temp_A
open table_cursor
while 1 = 1
begin
    fetch table_cursor into @Name
    if @@fetch_status <> 0 break

    select * from @Temp_B where contains(Name, @Name)
end
close table_cursor
deallocate table_cursor

You can create a join using a LIKE.. something like this:

SELECT * FROM TABLE_ONE 
FULL OUTER JOIN TABLE_TWO ON TABLE_ONE.String_Column LIKE '%' + TABLE_TWO.Name + '%'

ie - select everything from TABLE_ONE where the string_column is contained in the TABLE_TWO name