Drafts with the same title as deleted articles in mainspace

SQL

WITH SearchResults AS (
    SELECT 
        p.page_title,
        p.page_len,
        t.old_text AS content,
        -- Calculate the Levenshtein distance for each instance
        LEVENSHTEIN('pronunciation', 
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    LOWER(t.old_text),
                    'pronunciation',
                    1
                ),
                ' ',
                -1
            )
        ) as distance
    FROM page p
    JOIN revision r ON p.page_latest = r.rev_id
    JOIN text t ON r.rev_text_id = t.old_id
    WHERE p.page_namespace = 0  -- Main namespace only
    AND p.page_is_redirect = 0  -- Exclude redirects
    AND t.old_text REGEXP '[[:space:]][[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*[[:space:]]'  -- Rough pattern match
    AND t.old_text NOT LIKE '%pronunciation%'  -- Exclude correct spellings
)
SELECT 
    page_title AS article_name,
    page_len AS article_length,
    SUBSTRING(
        content, 
        GREATEST(1, LOCATE(
            (
                SELECT SUBSTRING(content, 
                    REGEXP_INSTR(content, '[[:space:]][[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*[[:space:]]'),
                    50
                )
            ),
            content
        ) - 50),
        100
    ) AS context,
    distance AS levenshtein_distance,
    COUNT(*) OVER () as total_instances
FROM SearchResults
WHERE distance BETWEEN 1 AND 3  -- Adjust threshold as needed
ORDER BY distance ASC, page_len DESC
LIMIT 10;
SQL

Checking query status...
