16

Now that the Table support feature Feature Preview: Table Support live, I would like to update my posts that still use the workarounds to show tables. Basically posts where I went nuts with dashes, pipe and plus symbols inside a code block to make it look like a table.

How can I find these posts of mine with a data explorer query? I prefer to have one complete list, across all sites, if that is possible.

14

If we assume most users will have done the ASCII art trickery inside a code block, with things like |-----+----+----| and variants, the following SEDE query will find your posts that have any of the following patterns in their body inside a code block:

  • ----
  • |--
  • --+--
  • --|--

Here is the query source:

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , id integer
                    , title nvarchar(250)
                    , body nvarchar(max)
                    )

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , p.id
     , q.title
     , p.body
from ', quotename(name), N'.dbo.users u
inner join ', quotename(name), N'.dbo.posts p on
  p.owneruserid = u.id
inner join ', quotename(name), N'.dbo.posts q on
  q.id = coalesce(p.parentid, p.id)
where accountid = ##accountid##
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)



select concat(
          'https://'
        , hostname
        , '/q/'
        , id
        , '|'
        , title) [Post] 
      , value
from #result
cross apply string_split('----,|--,--+--,--|--',',') line  -- patterns comma separted
where body like concat('%<code>%', line.value, '%</code>%')

The query needs your accountid that is found in the url of your network profile. The detected ascii art patterns are near the end, in the string_split function.

When run today this is the result for user einpoklum:

links to posts with in the second columns which pattern got detected

Keep in mind SEDE is updated once a week on Sunday.
Use the wonderful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

6
  • do you expect any hits on your last parameter that you don't already have on your second parameter? – Luuklag Dec 7 '20 at 13:12
  • @Luuklag yes/no .... I admit it might be more a capability demonstrator then anything else ... – rene Dec 7 '20 at 13:14
  • 2
    Wouldn't the first string catch horizontal rules? I think they can be four dashes? – Catija Dec 7 '20 at 13:38
  • 5
    @Catija not in posts.body because that is rendered html and the dashes would have been transformed to a <hr>. But it is a good call and it did cross my mind. It would have been a problem if I had used the textcolumn of PostHistory as that does hold original markdown. – rene Dec 7 '20 at 13:41
  • This is the minimum character sequence to create a table; not that it's legal (or useful), just that it is parsed as being correct (and generates a new table). --- If someone (even Community) edits an old post it will get chewed by The Machine, and rendered anew. – Rob Dec 7 '20 at 14:50
  • 2
    Ah, thanks for being so thorough and thoughtful about this! I really appreciate that you saw that this was something people might find helpful and created this Q&A about it - and even got it out before tables were live network-wide (which they are now!). – Catija Dec 7 '20 at 17:01

You must log in to answer this question.

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