Search code examples
sqlsql-serverdataexplorer

Splitting up tags in data explorer posts

I'm wanting to make a query which will list the tags ordered by the most unanswered questions (which have that tag), however as the tags are stored as space delimited varchar and as I understand it SQL Server (which StackExchange uses) doesn't have a function to split a string, I'm a bit unsure on how to proceed. I wrote this:

SELECT DISTINCT Tags from Posts WHERE AnswerCount = 0;

Which correctly returns the tags, however they're all grouped by post:

Results

I've looked around a lot but every result I find has creating a function as a solution to splitting the tags up, but you can't create functions on dataexchange so that isn't possible. Is there another way to get all the tags into one column?

Solution

  • You can do this with an inefficient join using like:

    select t.TagName, count(p.Tags)
from Tags t left join
     Posts p
     on p.Tags like '%' + t.TagName + '%'
where p.AnswerCount = 0
group by t.TagName;

    You should really use a separate junction table, PostTags with one row per post and per tag on that post. This type of query would be much, much simpler. SQL has a nice built-in mechanism for storing lists. It is not a string. It is called a table. You should use it, if you have the choice.