Posts containing images without alt text

3

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.

English

Q&A for linguists, etymologists, and serious English language enthusiasts

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 sites:
loading Hold tight while we fetch your results