5

I was trying to find a longest answer on a site, and ran into a weird discrepancy:

This query shows that this answer has a length of 36388 (length in the query is defined as LEN(Posts.Body))

Yet, the post clearly only has ~26000 characters, as measured 2 ways:

  • Copy/pasting full text of the answer in my text editor gives me 26040 characters (the source of the answer with all the markdown, not the rendered text which is even smaller).

  • Trying to post a new answer that consists of this answer's text twice gives me an error "Body is limited to 30000 characters; you entered 52258" - meaning that the length is under 26100 (not 100% sure where the difference in counts comes from as far as two methods)

Another example from same query: this answer shows len(body) of 33738 whereas the text of the answer has only 29000 characters.

This clearly isn't showing rendered characters, as the rendered character count is LOWER than actual answer text when editing (e.g. links add characters, as do images).

How can this data.SE discrepancy be explained?

3
  • @Tom - clarified in answer. Source with markdown. Also, while line endings are definitely a great thing to pay attention to, the answer was something like 100 lines, so even if dos/unix line endings are an issue, it can't possibly explain 10k character difference, sorry.
    – DVK
    Jul 3, 2017 at 13:33
  • I've tested it, the difference is just 87 chars.
    – Tom
    Jul 3, 2017 at 13:38
  • Well, it looks like the Database doesn't store markdown, but rather the actuall HTML code. You can see that by selecting the body content as well. Although it is cutted into a few chars, it already shows the content for the "top" question as <p>Note: Many smaller tidbits (<a href="https://web.archive.org/web/20110430144610/http://www.jkrowling.com/textonly/en/faq_view.cfm?id=113" rel="nofollow noreferrer">example</a>) are not mentioned in this list. Lots of these can be found on <a href="http://www.accio-quote.org" rel="nofollow noreferrer">Accio-Quote</a></p> <h2>Companion books</h2>.
    – Tom
    Jul 3, 2017 at 13:47

1 Answer 1

4

The difference between your result from the text editor and the result from the database query comes from the fact, that the database stores the HTML code of the post, whereas you only had the markdown version which needs far less characters.

You can see that by altering your query to

SELECT TOP 1 LEN(Body) AS Length, Body
FROM Posts WHERE PostTypeId =2 
ORDER BY Length DESC

This also selects the actual content of the Body column and you'll see the content for the top answer with the most characters as: <p>Note: Many smaller tidbits (<a href="http before the content has been cut down in the table.

Then you can recheck the actual length of the body by doing:

  • Download the query result as CSV file
  • remove everything that doesn't belong to the content of the Body column (CSV header, the data of the other column and the last quote on the last line)
  • replace all douple quotes with a single quote
  • make sure you're using Unix line endings

Then I have 36388 chars, like the query said.

If you download that query result as CSV, remove everything that doesn't belong to the Body column on top of the file and the additional quote in the last line, then

5
  • 1
    Use the posthistory table to get the length of the markdown: data.stackexchange.com/scifi/query/690852
    – rene
    Jul 3, 2017 at 15:31
  • @rene Looks like OP wants your comment as an answer for this question: meta.stackexchange.com/questions/297958/…
    – Tom
    Jul 3, 2017 at 16:06
  • I was more inclined to dupe vote that question against this one as they basically seem to handle the same issue/confusion.
    – rene
    Jul 3, 2017 at 16:26
  • Why does the last sentence trail off like that? Aug 31, 2018 at 17:48
  • @NathanTuggy To be honest, I have no idea. Maybe I submitted it too early, but never submitted an edit to complete the sentence. Pretty strange.
    – Tom
    Sep 17, 2018 at 14:30

You must log in to answer this question.

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