how do I use LIKE in SQL with multiple search patterns?

Question

I have a DB with multiple tables. I want to retrieve two columns as illustrated in the example below:

table name: stemp column name: strsmall Values: x po r

table name: btemp column name: str values: xam power powerful xtra poke

I want to have output like

strsmall str x xam p power p powerful x xtra p poke

There are hundreds of rows in each table in the actual database. the database I am using is oracle.

Answer 1

Try this:

select strsmall, str from stemp, btemp
where strsmall like '%'||str||'%'
or str like '%'||strsmall||'%'