Yahoo 知識+ 將於 2021 年 5 月 4 日 (美國東岸時間) 停止服務。從 2021 年 4 月 20 日 (美國東岸時間) 起,Yahoo 知識+ 網站將轉為僅限瀏覽模式。其他 Yahoo 資產或服務,或你的 Yahoo 帳戶將不會有任何變更。你可以在此服務中心網頁進一步了解 Yahoo 知識+ 停止服務的事宜,以及了解如何下載你的資料。

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

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 ? :(

1 個解答

評分
  • ?
    Lv 7
    9 年前
    最愛解答

    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'

還有問題嗎?立即提問即可得到解答。