CODEWITHSUNDEEP
sqlt-sql
Aaron Palmer
Aaron Palmer

Reputation: 8992

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

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.

Upvotes: 5

Views: 324

Answers (9)

onedaywhen
onedaywhen

Reputation: 57023

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

Upvotes: 0

dance2die
dance2die

Reputation: 36915

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

Upvotes: 0

Aaron Palmer
Aaron Palmer

Reputation: 8992

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.

Upvotes: 1

LukLed
LukLed

Reputation: 31842

Not so pretty, but it works:

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

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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)

Upvotes: 5

Tom A
Tom A

Reputation: 1682

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.

Upvotes: 0

Matthew Vines
Matthew Vines

Reputation: 27561

Assuming the two tables relate in some way.

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

Upvotes: 6

Raj More
Raj More

Reputation: 48016

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

Upvotes: 0

Mark Redman
Mark Redman

Reputation: 24515

I think you just need to remove the like eg:

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

Upvotes: 1

Related Questions