From: Campbell, Lance <lance@illinois.edu>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Help with a not match
Date: Fri, 9 Nov 2018 17:08:08 +0000
Message-ID: <47589FBF-7910-4F95-8239-BDF702A19B2C@illinois.edu> (raw)
In-Reply-To: <CAKFQuwaSZyaiKpo1wfWWnuwfdYvqnM3ePjNxPXBt+o5TNUHEFA@mail.gmail.com>
References: <E48BCA4C-5847-4634-8E9D-A91C9C4E40E6@illinois.edu>
<CAKFQuwaSZyaiKpo1wfWWnuwfdYvqnM3ePjNxPXBt+o5TNUHEFA@mail.gmail.com>
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" <david.g.johnston@gmail.com> wrote:
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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lance@illinois.edu, david.g.johnston@gmail.com
Subject: Re: Help with a not match
In-Reply-To: <47589FBF-7910-4F95-8239-BDF702A19B2C@illinois.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox