IN operator not finding matches

Question

I am not able to select values with IN clause using single quotes. The pid column is varchar(50).

select * from t_tra_main where pid in (200000002,300000394,200000004,
    200000001,300000378,300000393,300000379,200000003)

select * from t_tra_main where pid in ('200000002','300000394','200000004',
    '200000001','300000378','300000393','300000379','200000003')

The first query returns data, but the second query does not.

Are the pid values padded with leading zeroes? If so, that would explain it. When you use numeric values with the IN list, it implicitly converts the values (pid) to numeric which would strip off the leading zeroes. — mroach, May 11 '13 at 18:19
wasn't me who downvoted. Does = one of the existing ids work? Works with my sql-server 2012, so can only assume something weird going on, e.g. strange index on PID column, broken table (try DBCC on it?) Or none of those values are in the table. — Tony Hopkinson, May 11 '13 at 18:20
@TonyHopkinson : these records are present in table. i can select it with the first query. problem arises when pid contains alpha-numeric values such as M02000001 — Nitin Kabra, May 11 '13 at 18:23
Please post a repro on sqlfiddle.com. Without actual table definitions and real data that demonstrate the problem, the assumption is that the data is not what you expect it to be, and we aren't psychics, so we can't tell you what's wrong. — Aaron Bertrand, May 11 '13 at 18:27
There's something you aren't telling us, if the situation was as you described, both queries should work, though in my personal opinion the first one shouldn't, but MS don't agree. — Tony Hopkinson, May 11 '13 at 18:30
@NitinKabra . . . Both of the selects work on SQL Fiddle (http://www.sqlfiddle.com/#!6/d9dd5/3). Something is wrong with your description. — Gordon Linoff, May 11 '13 at 19:27
@GordonLinoff: [Modified your fiddle a little](http://www.sqlfiddle.com/#!6/1c71b/1) to show the problem. SQL Server seems to be doing an implicit type conversion from varchar (table type) to integer (query type). I suspect the OP mixed up his first and second queries. — Mike Sherrill 'Cat Recall', May 11 '13 at 20:16
@MikeSherrill'Catcall' . . . Interesting, but that is the inverse of what is in the question. The question says that it works without quotes but doesn't work with quotes. Perhaps that is backwards. — Gordon Linoff, May 11 '13 at 20:20

score 1 · Answer 1 · answered May 11 '13 at 20:26

A bit of experimentation, and I think the values in the table either have spaces or zeros at the beginning.

For the OP, you can test this by running the following query:

select *
from t_tra_main
where left(pid) in (' ', '0');

The SQL Fiddle is here. The code is:

create table t_tra_main (
  id int identity(1, 1),
  pid varchar(50)
);

insert into t_tra_main(pid)
    select '200000002' union all
    select '0300000394' union all
    select '0200000004' union all
    select ' 200000001' union all
    select ' 300000378' union all
    select '300000393 ' union all
    select '300000379 ' union all
    select '200000003';

select * from t_tra_main where pid in (200000002,300000394,200000004,
    200000001,300000378,300000393,300000379,200000003)

select * from t_tra_main where pid in ('200000002','300000394','200000004',
    '200000001','300000378','300000393','300000379','200000003')

IN operator not finding matches

1 Answers1