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

I think you just need to remove the like eg:

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

Answer 2

Name like Name? well, you cannot do a LIKE and IN at the same time.

This looks like a good candidate for SOUNDEX

Do a JOIN with a SOUNDEX.

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

Answer 3

Assuming the two tables relate in some way.

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

Answer 4

We ran into the same issue ourselves. It may not work for you, but the solution we came up with is: SELECT [Fields]
FROM [Table]
WHERE [Field] like 'Condition1'
OR [Field] like 'Condition2'


Not a great solution, but it works for us.

Answer 5

Not so pretty, but it works:

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

Answer 6

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 7

Thanks everyone. I used the following query, inspired by both LukLed's answer and a comment by Stuart Ainsworth.

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

Performance isn't that great, but it's a one time analysis and it gets the job done.

The reason I chose this over the "EXISTS" version is because it gives me both results from the new and old tables.

Answer 8

If you use very rarely used cross apply you can do this with ease.
(temp table declaration stolen code from Stuart)

2 tables do not need to have any relationship as Matthews' answer.

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

Rather than IN, use an EXISTS subquery. Quick example (SQL Server 2005 and above):

WITH oldTable ([name])
     AS
     (
      SELECT 'One'
      UNION ALL
      SELECT 'Day'
     ),
     newTable ([name])
     AS
     (
      SELECT 'onedaywhen'
      UNION ALL
      SELECT 'Aaron Palmer' 
     )
SELECT N1.[name]
  FROM newTable AS N1
 WHERE EXISTS (
               SELECT * 
                 FROM oldTable AS O1
                WHERE N1.[name] LIKE '%' + O1.[name] + '%'
              );

Answer 10

A mere stab in the dark but this is awfully reminiscent of a situation involving non-scalar data. Quick example using csv format (SQL Server 2005 and above):

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] + ',%' 
              );