question

ThoughtExperiment avatar image
ThoughtExperiment asked

LIKE, IN, and a Subquery in a Single SQL Statement

Hello everyone, 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!
tsqlctelike
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If any of the answers were helpful, please indicate all that were by clicking on the thumbs up next to those questions. If any answer solved your problem, please click on the check box next to that answer.
1 Like 1 ·
Usman Butt avatar image
Usman Butt answered
If I have got it right, then you have a table(two different tables would have the same solution) and you want to search one column values in another column? If that is correct then you can use LEFT JOIN and PATINDEX to overcome this problem. No need to do it manually. Something like SELECT CASE WHEN t2.Column1 IS NOT NULL THEN 'I am in Column1' ELSE text END FROM Table t1 LEFT JOIN Table t2 ON PATINDEX('%' + t1.text + '%', t2.Column1) > 0 Sorry I have not tested it. But this is just to give you the core idea and to show that there are some possible ways to do it ;)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NeerajTripathi avatar image
NeerajTripathi answered
As per my understanding, you want to search text in column1 and if it exists then you want to return 'I am in Column1 else the text you want to search from. Please check if below solves your problem. Select case when Count(*)>=1 then 'I am in column 1' Else 'Text' end from table Where column1 like '%Text%'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
It sounds like you're trying to write a catch-all query. I'd suggest reading [Gail Shaw's blog post][1] on this topic. She does a good job of summarizing your options. Short answer, you need to look to generating dynamic T-SQL statements. But read Gail's blog to understand how to do that correctly. [1]: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.