How can I write a T-SQL query to do a "like in"?

Question

I need to write a valid T-SQL query version of the following pseudo-code:

select * from newTable where [name] like in (
    select [name] from oldTable
)

I'm not sure how to go about this. Any help (even directing me to an existing question) would be great. Thanks!

Edit: Per some comments I will clarify this particular case. The tables look like this:

oldTable
code varchar(10)
name varchar(500)

newTable
code varchar(10)
name varchar(500)

In all of the cases where oldTable.code <> newTable.code, I am wanting to see if the oldTable.name is like one of the names in newTable.name. Basically, some of the new names have had qualifiers added to the beginning or end of the names. ie: 'old name' may have a 'qualified old name' in the newTable. Thanks again.

Answer 1

Assuming the two tables relate in some way.

SELECT newTable.* FROM newTABLE JOIN oldTable ON <JOIN CRITERIA>
WHERE newTable.[Name] LIKE oldTable.name

Answer 2

DECLARE @nt TABLE (NAME VARCHAR(10))
DECLARE @ot TABLE (NAME VARCHAR(10))

INSERT INTO @nt VALUES('Stuart')
INSERT INTO @nt VALUES('Ray')


INSERT INTO @ot VALUES('St%')
INSERT INTO @ot VALUES('Stu%')


SELECT *
FROM @nt n
WHERE EXISTS (SELECT *
                FROM @ot o
                WHERE n.name LIKE o.name)