✔ 最佳答案
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'