Search code examples
sqlsql-serversede

SQL to find results where a field contains http:// not preceded by <a href=" or <img src="?

I'm trying to create a SEDE query to find posts that contain links in plain-text, but the following query will be no good:

SELECT Id AS [Post Link], Body
FROM Posts
where Body LIKE '%http://%'
and Body not like '%<a href="%'
and body not like '%<img src="%'

Since it will exclude results where a post contains both a plain-text link and a valid hyperlink/image.

What is the correct approach to use? I tried searching to no avail.

Solution

  • For the purposes of your query, replace the data that you don't want to see with something that doesn't match your search pattern. For instance:

    SELECT Id AS [Post Link], Body
FROM Posts
WHERE REPLACE(
    REPLACE(
        Body,
        '<a href="http',
        'xxxx'
    ),
    '<img src="http',
    'xxxx'
)
LIKE '%http://%'