Alt image descript, TOP 5000

0

Please login or register to vote for this query.

(click on this box to dismiss)

Tridion

Q&A for Tridion developers and administrators

DECLARE @img_tag_start   VARCHAR(10) = '<img src="';
DECLARE @alt_blacklist   VARCHAR(80) = '%enter image description here%';
DECLARE @special_mention VARCHAR(80) = ##SpecialMention:string?%##;

WITH Search AS (
    SELECT *
         , CHARINDEX(@img_tag_start, Body) AS ImgTagStart
        FROM Posts
), ImgTags AS (
    SELECT *
         , SUBSTRING(Body, ImgTagStart, 1 + CHARINDEX('>', Body, ImgTagStart) - ImgTagStart) AS ImgTag
        FROM Search
        WHERE ImgTagStart > 0
), NoAltImgTags AS (
    SELECT *
         , SUBSTRING(ImgTag, 1 + LEN(@img_tag_start), CHARINDEX('"', ImgTag, 1 + LEN(@img_tag_start)) - LEN(@img_tag_start) - 1) AS ImgUrl
        FROM ImgTags
        WHERE
            ImgTag LIKE @alt_blacklist
            OR ImgTag NOT LIKE '%alt="%'
)
SELECT TOP 5000
      Id AS [Post Link]
     , ViewCount
     , CASE PostTypeId WHEN 1 THEN 'Q' WHEN 2 THEN 'A' END AS Type
     , Score
     , ImgUrl AS [Image URL]
     , LastActivityDate
     , CASE WHEN @special_mention <> '%' AND UPPER(Body) LIKE CONCAT('%', UPPER(@special_mention), '%') THEN N'✓' END AS [Special Mention]
    FROM NoAltImgTags
    ORDER BY Score DESC, PostTypeId;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results