LIKE operator with a SUBQUERY_1－香港美食2017

標題:

LIKE operator with a SUBQUERY

發問:

Using the LIKE operator with a SUBQUERY!! how do i issue the query that will use the values coming from another table as patterns... something like this...select field1 from table1 where field2 like '%(select pattern1 from table2)%'where:table1 field1 field2 1 ABC 2 BCD ... 顯示更多 Using the LIKE operator with a SUBQUERY!! how do i issue the query that will use the values coming from another table as patterns... something like this... select field1 from table1 where field2 like '%(select pattern1 from table2)%' where: table1 field1 field2 1 ABC 2 BCD 3 DDD 4 BBB table2 pattern1 A C F and so result set will be: field1 1 2 is this possible? and if so, what is the correct SQL ? :(

最佳解答:

First of all, you cannot use your subquery even without the "%" wildcard character. select field1 from table1 where field2 like (select pattern1 from table2) The subquery returns more than one row. You will get this error in SQL Server: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. You have to use "IN" when the subquery returns more than row. select field1 from table1 where field2 IN (select pattern1 from table2) or Change the subquery so that it returns only one row select field1 from table1 where field2 LIKE (select pattern1 from table2 where pattern1='C') Can you use "%" with subquery? The answer is no. If you want to do it, you need to create a stored procedure and execute the stored procedure. CREATE PROCEDURE FindPattern @pattern1 varchar(20) AS DECLARE @allpattern varchar(20); SET @allpattern = select pattern1 from table2 where pattern1=@pattern1 SET @allpattern = '%' + @allpattern + '%' select field1 from table1 where field2 LIKE @allpattern GO EXEC FindPattern 'C'