Re: Trouble with IN operator

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.costcenter LIKE '%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