Like with a subselect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Like with a subselect

Author

Topic

Mickey13
Starting Member

4 Posts

Posted - 2002-08-06 : 03:50:00

I want to use LIKE on a subselect from a temporary table. Something like this:

SELECT *
FROM myTable
WHERE myWord LIKE (SELECT word FROM #myTempTable)

only this doesn't work :-)

So what should I do?

Crespo

85 Posts

Posted - 2002-08-06 : 05:38:03

As you said in your post, your query does not work because your subselect is returning more than one value. Of course you can use EXISTS instead of LIKE but that would only return MATCHING values rather than SIMILAR ones.
I suggest you make a cursor for this one. It should do the trick.

Hope this helps.

Good Luck!

Crespo.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-06 : 05:58:09

If you want a row for each pattern matched in #myTempTable then use a join:


SELECT A.*, B.word
FROM myTable AS A
INNER JOIN #myTempTable AS B ON A.myWord LIKE B.word

If you want each row in myTable that matches something in #myTempTable use a subquery:


SELECT *
FROM myTable AS A
WHERE EXISTS (
    SELECT *
    FROM #myTempTable AS B
    WHERE A.myWord LIKE B.word
  )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources