你想要什么 like 相反的操作数。此外，应该使用单引号来定义字符串文字，而不是双引号。
所以：

select
    t.*,
    case when columnY like '%' + columnX + '%'
        then 'Yes'
        else 'No'
    end newColumn
from mytable t

你也可以使用 charindex() :

case when charindex(columnX, columnY) > 0 then 'Yes' else 'No' end

你可以用以下方法

SELECT *, CASE WHEN ColY LIKE CONCAT('%', ColX, '%')
               THEN 'Yes'
               ELSE 'No'
          END NewColumnUsingLike,
          CASE WHEN PATINDEX(CONCAT('%', ColX, '%'), ColY) > 0
               THEN 'Yes'
               ELSE 'No'
          END NewColumnUsingPatIndex,
          CASE WHEN CHARINDEX(ColX, ColY) > 0
               THEN 'Yes'
               ELSE 'No'
          END NewColumnUsingCharIndex
FROM
(
  VALUES
  ('apple',     'the apple is red'),   
  ('orange',    'orange tree is tall'),
  ('berry',     'the sky is blue')
) T(ColX, ColY);

在线演示