Using LIKE and IN and a Subquery in a single SQL Statement

Question

I am writing a query in which I am trying to search a subquery/CTE for a wildcard substring, and nesting this logic in my CASE statement. For example:

SELECT
CASE 
WHEN '%' + text + '%' IN (SELECT Column1 FROM Table) THEN 'I am in Column1'
ELSE text END
FROM Table

Unfortunately, it looks like there is no possibly way to do this. Since I would need to use the LIKE operator and there is no way to use both LIKE and IN. I would have to write each LIKE statement separately, and that would be for 1000+ rows. Does anyone recommend a more immediate solution? Thanks kindly in advance!

-- Edit: Sorry, some clarifications per comments. A better example:

UserID     |  UserPeers   |  Gender
--------------------------------------------
Mike       |  Tom1, Bob1  |  M
John       |  Tom1, Greg1 |  M
Sally      |Mike1, John1  |  F
Sara       | Sally1, Bob1 |  F

In the above table, I need to search the substrings in UserPeers columns to see if they exist anywhere in the UserID column. The rows that would be successfully returned in this case would be the ones under Sally and Sara, since 'Mike' and 'Sally' exist under UserID.

SELECT *
FROM Users
WHERE '%' + UserPeers + '%' LIKE (SELECT UserID FROM Users)

The error returned here is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

SELECT UserID, CASE WHEN EXISTS 
(
  SELECT 1 FROM dbo.Users WHERE UserPeers LIKE '%' + u.UserID + '%'
) THEN 'I am in Column1' ELSE UserID END
FROM dbo.Users AS u;