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!
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.
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 ;)
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%'
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/
16 People are following this question.
