2

While trying to write a query on https://data.stackexchange.com/datascience/, using the Posts table, I cannot filter by tags. For instance, if I try to extract the tags from the Posts table:

select tags
from Posts

I get a table with a row containing 'machine-learning'.

However, if I then filter the table using that tag, the resulting table is empty.

select *
from Posts
where tags = 'machine-learning'

1 Answer 1

8

When I write SQL queries on an unfamiliar database, I tend to check how filters work on records I expect to satisfy (part of) the query. That should make one notice how the Posts.Tags results look like - it contains all tags of the question, separated by angle brackets, e.g. <machine-learning><another-tag>.

However, in this particular case, the angle brackets are difficult to notice, because TagName is a magic column with special formatting - you should try using the option 'Text-only results' to see the difference.

The query you're looking for is

select *
from Posts
where tags like '%<machine-learning>%'

or, if that is not performant enough, join with PostTags and Tags and filter on Tags.TagName. For more information, please have a look at the schema documentation.

4
  • One might be able to get some additional speed up by collating the tags column as Latin1_General_BIN. fact-check needed. I am a SQL newbie.
    – starball
    Nov 18 at 18:41
  • @starball I'll defer to rene for that. I'm happy to know how to account for collation, how that affects performance is out of my league ...
    – Glorfindel Mod
    Nov 18 at 18:53
  • 1
    @starball I don't think it will work on some language sites, like Japanese.SE? Nov 19 at 2:55
  • @starball if performance is an issue, the joining with posttags is probable the way to go. If you still feel you need a collation, pick one that the row is already in so SQL doesn't have to do a re-encoding for every row in the result. There is still something to desire for SEDE default collation: meta.stackexchange.com/questions/276221/…
    – rene
    Nov 19 at 13:56

You must log in to answer this question.

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