10
\$\begingroup\$

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 '%</code></pre>%'
  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 %</code></pre>% - 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 && object.attribute && 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:

<p>I didn't write this but I noticed it in our codebase at work.</p>

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

<p>I get that the programmer is trying to check that the variable <code>object.attribute.data</code>is defined before using it, but is
seems like so much repeated code. What's the best way to assign
<code>data</code> the value of <code>object.attribute.data</code> if
it's defined, otherwise giving it a value of <code>{}</code>?</p>
\$\endgroup\$
  • \$\begingroup\$ This would fail for a question where someone uses only the tag <code>. \$\endgroup\$ – Ismael Miguel Jun 8 '15 at 21:55
  • 1
    \$\begingroup\$ @IsmaelMiguel you are supposed to use 4 spaces, if you highlight your code and click the code button it will add 4 spaces to every line so that the markup is done for you. And if you only use <code> tags then you aren't formatting it correctly, and that should be handled by an edit anyway, so this query is still serving it's purpose. \$\endgroup\$ – Malachi Jun 9 '15 at 12:50
  • \$\begingroup\$ @Lyle'sMug Observe the following code: pastebin.com/5xdJXLDk. That one is still valid, and it has the code (almost) properly formated, with missing whitespaces for identation. And this query would fail in it. And yes, the syntax highlighter 'works' with it. \$\endgroup\$ – Ismael Miguel Jun 9 '15 at 13:38
  • 1
    \$\begingroup\$ @IsmaelMiguel - what Lyle'sMug is saying, and I agree with, is that a 'false positive' indicates that there's a problem in the markdown for the question anyway, and an edit to use the standard markdown will both fix the question, and remove it from the SEDE query results.... which is a good thing, \$\endgroup\$ – rolfl Jun 9 '15 at 13:44
  • 1
    \$\begingroup\$ @IsmaelMiguel - Let's take this to chat The 2nd Monitor \$\endgroup\$ – rolfl Jun 9 '15 at 13:54
7
\$\begingroup\$

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('</code></pre>', 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 '%</code></pre>%'

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.