CodeLess Questions query As part of an exercise by Jamal to clean up old questions on Code Review that have no code in them, I helped by writing this query on Stack Exchange Data Explorer: CodeLess Questions

select Id as [Post Link], CreationDate
from Posts
where PostTypeId = 1 --questions
  and Body not like '%&lt;/code&gt;&lt;/pre&gt;%'
  and ClosedDate is null
order by CreationDate


I realize this is a relatively simple query, but I am always looking for ways to avoid like conditions (and not like), and this query irks me.

Note that the like condition is %&lt;/code&gt;&lt;/pre&gt;% - this is to trap the automatically managed code blocks that Stack Exchange uses to identify code. I cannot easily trap the open-tags because sometimes, when a custom syntax is used, the open tags are hard to search on.

I am looking for insights on whether the query can be improved or whether there are alternatives to the systems I have used for identifying code-less questions.

For reference, when Stack Exchange gets a question, the markup is converted to HTML and stored in the Body column.

An example is as follows:

MarkDown:


I didn't write this but I noticed it in our codebase at work.

    var data = object &amp;&amp; object.attribute &amp;&amp; object.attribute.data ? object.attribute.data : {};

I get that the programmer is trying to check that the variable
`object.attribute.data` is defined before using it, but is seems like
so much repeated code. What's the best way to assign `data` the value
of `object.attribute.data` if it's defined, otherwise giving it a
value of `{}`?



HTML Encoding:


&lt;p&gt;I didn't write this but I noticed it in our codebase at work.&lt;/p&gt;

&lt;pre&gt;&lt;code&gt;var data = object &amp;amp;&amp;amp; object.attribute &amp;amp;&amp;amp; object.attribute.data ? object.attribute.data : {};
&lt;/code&gt;&lt;/pre&gt;

&lt;p&gt;I get that the programmer is trying to check that the variable &lt;code&gt;object.attribute.data&lt;/code&gt;is defined before using it, but is
seems like so much repeated code. What's the best way to assign
&lt;code&gt;data&lt;/code&gt; the value of &lt;code&gt;object.attribute.data&lt;/code&gt; if
it's defined, otherwise giving it a value of &lt;code&gt;{}&lt;/code&gt;?&lt;/p&gt;
I have never used it before but I think what you are looking for is CHARINDEX I found a StackOverflow Answer that has several upvotes and no downvotes using it like this

WHERE CHARINDEX('&lt;/code&gt;&lt;/pre&gt;', Body) = 0


So far from what I have seen they are relatively close to the same speed, and is giving the same results as your query.  

I would much rather read this than Body not like '%&lt;/code&gt;&lt;/pre&gt;%'

I think we have found a new "hammer", let's find some nails.

Read more about CHARINDEX Here



There is also a CONTAINS function in T-SQL, but unfortunately it cannot be used on that column of that table because the column is not Full-Text Indexed

Read more about CONTAINS Here