Обсуждение: [NOVICE] Trouble with IN operator

Chuck Roberts <croberts@gilsongraphics.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

. And now it's critical. I'm using the psql tool

in a Windows 7 command window, version is 7.4.6.

I wonder if I'm missing something basic, which is not even in the manuals

I've beeI've already googled to find some help but have found no info or

examples on using IN with an array of strings. So here's what I'm trying to

do:

AND (TRIM(tbl.costcenter)  IN ('540'))

This results on no error, but also no records. When I take this simple

clause out, I get records. 

On Fri, Feb 03, 2017 at 01:48:49PM -0500, Chuck Roberts wrote:
> 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)')

Try this:

SELECT DISTINCT char_length(tbl.costcenter), octet_length(tbl.costcenter), tbl.costcenter FROM blah WHERE
tbl.costcenterLIKE '%540%'; 

Maybe remove the LIKE comparison if it's not matching even on that.

This will helps tell you if your assumptions about the data in the DB are
correct. "should" (which you used above) is a fun-filled word. :)

--
  "A search of his car uncovered pornography, a homemade sex aid, women's
  stockings and a Jack Russell terrier."
    - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480

On Fri, Feb 03, 2017 at 01:48:49PM -0500, Chuck Roberts wrote:
> 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)')