Re: Help with a not match

I am trying to find IDs where the field content contain improper substrings.  

I want something like this:
SELECT id from tableA WHERE content LIKE "%files/" || {some number that does not match the ID} || "/%" 

I don't know how to write this.

The SQL you provided is the opposite of what I was looking for.  Thanks though.

Lance 

On 11/9/18, 10:54 AM, "David G. Johnston" <[email protected]> wrote:

    On Fri, Nov 9, 2018 at 9:46 AM Campbell, Lance <[email protected]> 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 second field 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 id for that record.  I want to find any rows where content contains “/files/###/” where the ### does not match the id for the row.  Also note that the id could be 1 to five digits.  Also there could be good and bad substrings in the content field.
    > 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.