Re: Trouble with IN operator

Yes there is data that matches the criteria of the IN condition. When I
remove the clause with the IN, I get all kinds of records that look like
they match the criteria. The tbl.costcenter is a string of 3 characters,
but it only contains numbers which are zero padded, like '001', '540',
'900'. There should be no room for odd characters, even though users do
enter this number.

Also I tried a case-insensitive regex, and that didn't work either. Ex:
AND (tbl.costcenter ~* '(540|001|900)')

Thanks!

On Fri, Feb 3, 2017 at 1:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Chuck Roberts <croberts(at)gilsongraphics(dot)com> writes:
> > I think this might be a data problem. If anyone else has an idea, it's
> > welcome.
>
> Well, the obvious question is, are you sure you have records that *should*
> match the condition?
>
> Plain "trim" is not very bright, it will only remove characters that are
> plain ASCII spaces. I'm wondering a bit about whether there's carriage
> returns, tabs, non-breaking spaces, or other weird kinds of whitespace in
> your data, which might make it so records that look like they contain
> "540" don't actually match.
>
> regards, tom lane
>