T-SQL сопоставление с исключениями

Question

Вот проблема, с которой я неоднократно сталкивался, играя в Stack Exchange Data Explorer, основанный на T-SQL:

Как искать строку, кроме случаев, когда она встречается как подстрока какой-либо другой строки?

Например, как я могу выбрать все записи в таблице MyTable где колонна MyCol содержит строку foo, но игнорируя любые fooс, которые являются частью строки foobar?

Быстрая и грязная попытка была бы что-то вроде:

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

но, очевидно, это не будет соответствовать, например, MyCol = 'not all foos are foobars', который я хочу соответствовать.

Одно решение, которое я придумала, - это заменить все случаи foobar с каким-то фиктивным маркером (который не является подстрокой foo), а затем проверять наличие foos, как в:

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

Это работает, но я подозреваю, что это не очень эффективно, так как он должен запустить REPLACE() на каждой записи в таблице. (Для SEDE это обычно Posts таблица, которая в настоящее время имеет около 30 миллионов строк.) Есть ли лучший способ сделать это?

(FWIW, реальный случай использования, который вызвал этот вопрос, искал SO сообщения с URL-адресами изображений, которые используют http:// Префикс схемы, но не указывает на хост i.stack.imgur.com.)

Answer 1

Ни один из приведенных способов не гарантированно работает так, как рекламируется, и выполняет только REPLACE на подмножестве строк.

SQL Server не гарантирует короткое замыкание предикатов и может переместить вычислительные скаляры в базовый запрос для производных таблиц и CTE.

Единственное, что (в основном) гарантировано работает, это CASE заявление. Ниже я использую синтаксический сахарный сорт IIF который расширяется до CASE

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

Answer 2

Трехступенчатый фильтр должен работать:

1. собрать все строки, соответствующие "%foo%";

2. заменить все экземпляры 'foobar' на не встречающуюся строку (например, '' возможно);

3. Проверьте еще раз на соответствие "%foo%"

Здесь вы выполняете REPLACE только для потенциально совпадающих строк, а не для всех. Если вы ожидаете только небольшой процент совпадений, это должно быть гораздо более эффективным.

SQL будет выглядеть так:

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

Обратите внимание, что подзапрос обязателен, поскольку в SQL нет ярлыков для выражений; Движок может свободно переупорядочивать булевы термины по мере необходимости для эффективной обработки на уровне одного запроса.

Answer 3

Это будет быстрее, чем ваш текущий запрос:

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

ЗАМЕНА рассчитывается после применения MyCol, поэтому это быстрее, чем просто:

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

Answer 4

Предполагая, что вы заинтересованы только в поиске случаев foo с окружающими их пространствами

 SELECT * 
 FROM MyTable 
 WHERE MyCol LIKE 'foo %' OR MyCol LIKE '% foo %' OR MyCol LIKE '% foo'