Match a pattern that does not contain a specific subpattern

Question

I want to find the records that match a given text pattern but where a section of the pattern is different from a specific value. For example, a record of the Page table could have a PageId field and a Description text field that look like:

PageId: 3718
Description: Page 3718 contains... Page 3718 contains...

I know that I can build a LIKE condition to match the PageId:

Description LIKE '%Page ' + CAST(P.PageId as varchar(8)) + ' contains%'

But what I want is the opposite. I want to find the records where at least one page ID in the Description field does not match the actual PageId value. That would enable me to detect records like the following:

PageId: 3718
Description: Page 4092 contains... Page 3718 contains...

I know how I would do it using a regular expression in code but how can I do it in SQL?

Answer 1

You can use something like:

REPLACE(x, 'Page ' + CAST(PageId as varchar(8)) + ' contains', '') LIKE '%Page%contains%'

to try to match the odd strings.

Note that this will match too much on string containing word 'contains' outside of the 'page contains' pattern.

Answer 2

With an example...

CREATE TABLE SQL_Server_Query
(PageId INT PRIMARY KEY,
 "Description" VARCHAR(max));

INSERT INTO SQL_Server_Query VALUES
(3718, 'Page 3718 contains... Page 3718 contains...: Page 3718 contains... Page 3718 contains...'),
(3719, 'Page 4092 contains... Page 3719 contains...');

The query can be:

SELECT PageId
FROM   SQL_Server_Query 
       CROSS APPLY STRING_SPLIT(REPLACE("Description", 'Page ', CHAR(0)), CHAR(0))
WHERE  value NOT LIKE CAST(PageId AS VARCHAR(32)) + '%'
  AND  value <> ''