Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 157 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

I was thinking that a query to find posts with newlines followed by end-brackets might be useful for finding posts with code formatting issues-- for example, posts that look like this:

Here is what I have so far:

function foo() {
    return bar;

So I tried searching for posts with this in my query:

where body like '%\r\n}%'

However Data Explorer seems to not like that query (it eventually tells me something is wrong with the server). I also tried '%\r}%' (same result) and '%\n}%'. The query with '%\n}%' yielded results, but it isn't what I expected... The posts in the result set contain the literal \n}, not simply a line break in the post followed by a }.

I then tried:

where body like '%

This got me closer, but I was seeing posts that were properly formatted with <code>, <pre>, and/or spaces/tabs, so I tried to refine the query by adding:

and body not like '%<code>%'
and body not like '%<pre>%'
and body not like '%    %'
and body not like '%\t%'

That got rid of the posts with those tags, but judging by the result set, the query seems to match any number of optional spaces between the \n and the }, despite the first part of the WHERE clause and despite the part designed to filter out four consecutive spaces.

This means the query still matches posts like this one which are in fact properly formatted, in addition to the desired improperly formatted posts. I haven't yet confirmed whether the '%\t%' actually does anything.

Is there any way I can modify this query to search for what I want, or does Data Explorer always ignore whitespace like this?

share|improve this question
up vote 1 down vote accepted

If you look at the contents of the Post column, beware that non-formatted things like:

Here is what I have so far:

function foo() {
  return bar;

...are stored as:

<p>Here is what I have so far:</p>

<p>function foo() {
  return bar;

(For which the code block would actually show on a single line in the browser.)

However, proper Markdown formatting of the code block would yield something like:

<pre><code>function foo() {
  return bar;

(For some reason, I see a lot of uppercase tags too.)

To tell those apart, a newline followed by a } is not what you're after. Instead, you'd need:

select top 10 Id as [Post Link], Body
from Posts
where Body like '%}</p>%'​​​​​​​​​​​​​

Or, to really limit to lines with a single curly bracket:

select top 10 Id as [Post Link], Body
from Posts
where Body like '%' + char(10) + '}</p>%'​​​​​​​​​​​​

The server being Windows, I assumed a newline would be CRLF (char(13) + char(10)), but testing makes me think it's just LF shows most posts use just LF. Maybe it's stored exactly as the browser sends it? (2,095 posts use CRLF; exactly one post did match %CRLF}, but searching for %LF} will find that one too of course, and you already fixed that one...)

(The above, by the way, revealed many posts that you already fixed!)

share|improve this answer
Seems to do the trick. I never would have guessed that. Thanks! – Jason Plank Nov 1 '11 at 0:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .