Posts containing images without alt text

4

Please login or register to vote for this query.

(click on this box to dismiss)

This query lists all questions and answers that contain an image without any alt text, or that have the standard placeholder text. If there are multiple images in a post, only the first image is considered.

Economics

Q&A for those who study, teach, research and apply economics and econometrics

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 Id AS [Post Link]
     , 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