Re: Help with a not match

On Fri, Nov 9, 2018 at 9:46 AM Campbell, Lance <lance@illinois.edu> wrote:
> Use case:
> I have a table I will call “tableA”.  It has two columns.  One column is an “id” that is an integer primary key.  The
secondfield is a text field called “content”. 
> In the “content” field there may or may not be this substring “/files/{id}/” .  Note that {id} represents the field
idfor that record.  I want to find any rows where content contains “/files/###/” where the ### does not match the id
forthe row.  Also note that the id could be 1 to five digits.  Also there could be good and bad substrings in the
contentfield. 
> Example where a row is fine:
> Id=123
> Content=”abc”
> Id=345
> Content=”abc files/345/ abc files/345/”
> Example where a rows are not good:
> Id=123
> Content=”abc files/456/”
> Id=123
> Content=”abc files/123/ abc files/456/”
> Could you please help me with the proper way to write this SQL statement?
> Select id from tableA where content like …

Personally I'd use a regular expression:

... WHERE content ~ '/' || id || '/';

It not obvious why your fourth example is not good - the pattern above
will see files/123/ and be happy.

David J.