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

Question

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

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

I am not sure how to do this. Any help (even directing me to an existing question) would be wonderful. Thanks!

Edit: For 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 cases where oldTable.code <> newTable.code, I want to see if oldTable.name looks like one of the names in newTable.name. Basically, some of the new names have added qualifiers to the beginning or end of the names. i.e.: "old name" may have "qualified old name" in newTable. Thanks again.

Answer 1

, - .

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)

Answer 3

, :

SELECT DISTINCT newTable. *
FROM newTABLE
oldTable
ON newTable. "Name" LIKE oldTable.name

Answer 4

, , :

* newTable, [Name] in ( oldTable)

Answer 5

. , LukLed, .

SELECT DISTINCT old.code, old.name, new.name, new.code 
FROM newTable new 
JOIN oldTable old
ON new.name LIKE '%' + old.name + '%' 
WHERE new.code <> old.code
ORDER BY old.name, new.name

, , .

, "EXISTS", , .

Answer 6

, ? , LIKE IN .

SOUNDEX

JOIN SOUNDEX.

: http://msdn.microsoft.com/en-us/library/aa259235%28SQL.80%29.aspx

Answer 7

. , , , : SELECT [Fields]
FROM [Table]
WHERE [Field] like 'Condition1'
OR [Field] like 'Condition2'


, .

Answer 8

cross apply, .
( temp )

2 - Matthews.

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  distinct n.NAME
from    @nt n
    cross apply @ot o
where   n.NAME like o.name

Answer 9

A simple act in the dark, but it terribly reminds a situation with non-scalar data. A quick example using the csv format (SQL Server 2005 and later):

WITH oldTable ([name])
     AS
     (
      SELECT '003,006,009,012,015'
      UNION ALL
      SELECT '005,015'
     ),
     newTable ([name])
     AS
     (
      SELECT '007'
      UNION ALL
      SELECT '009'
      UNION ALL
      SELECT '015'
     )
SELECT N1.[name]
  FROM newTable AS N1
 WHERE EXISTS (
               SELECT * 
                 FROM oldTable AS O1
                WHERE ',' + O1.[name] + ','
                         LIKE '%,' + N1.[name] + ',%' 
              );