Using LIKE and IN and a Subquery in a single SQL Statement
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;
dmedz
Updated on July 16, 2022Comments
-
dmedz almost 2 years
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.
-
Adir D about 11 yearsCount can be quite expensive and wasteful when you don't care about the actual count.
EXISTS
is guaranteed to be no worse but can often be much better. -
Adir D about 11 yearsDon't you mean to correlate the subquery?
-
ljh about 11 yearsYes, I mean correlate the subquery, but I'm not sure @thoughtExperiment need a key=key.
-
Adir D about 11 yearsNot sure either. If I were given these requirements I would have thrown them back; in this case all we can do is guess. :-)