T-SQL pattern matching with exceptions

Question

Here's a problem I've repeatedly encountered while playing with the Stack Exchange Data Explorer, which is based on T-SQL:

How to search for a string except when it occurs as a substring of some other string?

For example, how can I select all records in a table MyTable where the column MyCol contains the string foo, but ignoring any foos that are part of the string foobar?

A quick and dirty attempt would be something like:

SELECT * 
FROM MyTable 
WHERE MyCol LIKE '%foo%' 
  AND MyCol NOT LIKE '%foobar%'

but obviously this will fail to match e.g. MyCol = 'not all foos are foobars', which I do want to match.

One solution I've come up with is to replace all occurrences of foobar with some dummy marker (that is not a substring of foo) and then checking for any remaining foos, as in:

SELECT * 
FROM MyTable 
WHERE REPLACE(MyCol, 'foobar', 'X') LIKE '%foo%'

This works, but I suspect it's not very efficient, since it has to run the REPLACE() on every record in the table. (For SEDE, this would typically be the Posts table, which currently has about 30 million rows.) Are the any better ways to do this?

(FWIW, the real use case that prompted this question was searching for SO posts with image URLs that use the http:// scheme prefix but do not point to the host i.stack.imgur.com.)

Answer 1

Neither of the ways given so far are guaranteed to work as advertised and only perform the REPLACE on a subset of rows.

SQL Server does not guarantee short circuiting of predicates and can move compute scalars up into the underlying query for derived tables and CTEs.

The only thing that is (mostly) guaranteed to work is the CASE statement. Below I use the syntactic sugar variety of IIF that expands out to CASE

SELECT *
FROM   MyTable
WHERE  1 = IIF(MyCol LIKE '%foo%', 
               IIF(REPLACE(MyCol, 'foobar', 'X') LIKE '%foo%', 1, 0), 
               0);

Answer 2

A three-stage filter should work:

1. collect all rows matching '%foo%';

2. replace all instances of 'foobar' with a non-occurring string (such as '' perhaps);

3. Check again for matching '%foo%'

Here you only perform the REPLACE on potentially matching rows, not all rows. If you are expecting only a small percentage of matches, this should be much more efficient.

SQL would look like this:

;with data as (
    select * 
    from MyTable 
    where MyCol like '%foo%'      
)
select *
from data
where replace(MyCol, 'foobar', 'X') like '%foo%'

Note that a sub-query is required, as there are no expression short-cuts in SQL; the engine is free to reorder Boolean terms as desired for efficient processing within a singe query level.

Answer 3

This will be faster than your current query:

SELECT * 
FROM MyTable 
WHERE 
  MyCol like '%foo%' AND
  REPLACE(MyCol, 'foobar', 'X') LIKE '%foo%'

The REPLACE is calculated after MyCol has been applied, so this is faster than just:

REPLACE(MyCol, 'foobar', 'X') LIKE '%foo%'