Posts containing images without alt text


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.

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

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
            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

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