From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "Campbell, Lance" <lance(at)illinois(dot)edu> |
Cc: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Help with a not match |
Date: | 2018-11-09 16:54:23 |
Message-ID: | CAKFQuwaSZyaiKpo1wfWWnuwfdYvqnM3ePjNxPXBt+o5TNUHEFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, Nov 9, 2018 at 9:46 AM Campbell, Lance <lance(at)illinois(dot)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 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2018-11-09 17:08:08 | Re: Help with a not match |
Previous Message | Campbell, Lance | 2018-11-09 16:46:33 | Help with a not match |