Tag wikis / excerpts containing certain words

1

Please login or register to vote for this query.

(click on this box to dismiss)

EOS.IO

Q&A for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO

-- result table, don't rename and keep the site column
CREATE TABLE #results (site NVARCHAR(250),
                       tagname NVARCHAR(50),
                       type NVARCHAR(20),
                       tagid INT,
                       body NVARCHAR(max));

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''', t.TagName, ''Excerpt'', t.Id, p.Body
  FROM ' + quotename(name) + '.dbo.Tags AS t
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS p
    ON p.Id = t.ExcerptPostId
  WHERE p.Body LIKE ''%' + ##query:string## + '%'' ESCAPE ''\'' COLLATE sql_latin1_general_cp1_ci_ai
  
UNION ALL

SELECT ''' + name + ''', t.TagName, ''Wiki'', t.Id, p.Body
  FROM ' + quotename(name) + '.dbo.Tags AS t
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS p
    ON p.Id = t.WikiPostId
  WHERE p.Body LIKE ''%' + ##query:string## + '%'' ESCAPE ''\'' COLLATE sql_latin1_general_cp1_ci_ai
'
FROM sys.databases
WHERE database_id > 5

-- execute it
EXEC (@sql)

SELECT site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '/edit-tag-wiki/' + CAST(tagid AS NVARCHAR) + '|' + tagname + ', ' + type,
  body
  FROM #results
  ORDER BY site, tagname
     
DROP TABLE #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results