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.

Please clarify with some example data. What about ANDing the conditions? — OldProgrammer
– OldProgrammer, Commented Mar 31, 2013 at 22:15
Not sure I follow. Do you just want to compare the text column with Column1 in the same row? Is text a variable or a column name? — Aaron Bertrand
– Aaron Bertrand, Commented Mar 31, 2013 at 22:15
Thank you all for the extensive responses, Aaron's initial answer did the trick. Got stuck on the LIKE. Thanks a bunch! — dmedz
– dmedz, Commented Mar 31, 2013 at 22:49

Aaron Bertrand · Accepted Answer · 2013-03-31 22:48:51Z

3

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;

edited Mar 31, 2013 at 22:48

answered Mar 31, 2013 at 22:20

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-03-31 22:19:12Z

0

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.

answered Mar 31, 2013 at 22:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Dave Jorgenson · Accepted Answer · 2013-03-31 22:19:59Z

0

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

answered Mar 31, 2013 at 22:19

Dave Jorgenson

564 bronze badges

1 Comment

Aaron Bertrand Over a year ago

Count 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.

ljh · Accepted Answer · 2013-03-31 22:29:13Z

0

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

edited Mar 31, 2013 at 22:29

answered Mar 31, 2013 at 22:19

ljh

2,6341 gold badge16 silver badges21 bronze badges

3 Comments

Aaron Bertrand Over a year ago

Don't you mean to correlate the subquery?

ljh Over a year ago

Yes, I mean correlate the subquery, but I'm not sure @thoughtExperiment need a key=key.

Aaron Bertrand Over a year ago

Not sure either. If I were given these requirements I would have thrown them back; in this case all we can do is guess. :-)

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related