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

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

Here is one approach:

SELECT (CASE WHEN exists (select 1 from table t2 where t2.column1 like '%' + t.text + '%')
             then 'I am in Column1'
             ELSE t.text
        END)
FROM Table t;

Your original query seemed to have the wildcards on the wrong side of the like.

Answer 2

You could try something like this:

select case when (select count(*) from table where column1 like ('%' + text + '%')) > 0 then 'I am in column1' else text end from table

Answer 3

Probably you don't really need Like and IN

SELECT
CASE 
WHEN EXISTS (SELECT 1 FROM Table B WHERE B.COLUMN1 LIKE '%' + A.TEXT+ '%') THEN 'I am in Column1'
ELSE A.text END
FROM Table A 

Answer 4

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;