Why do my SQL queries not agree with each other?

Hoping this is something I can fix, or maybe someone can point out an obvious mistake.

I have two queries:

SELECT HIDEORSHOW FROM tblProspects WHERE PROSPECT_ID = 1261484;

HIDEORSHOW
1

SELECT PROSPECT_ID FROM tblProspects WHERE HIDEORSHOW = 1;

PROSPECT_ID
196248
199004
204190
204338
210918
211932
213332
214186
216980
218254
222420
223578
223824
224429
224390
224672
224714
227031
227481
228363
230040
238168
239230
240790
241409
243827
244553
245785
247947
248349
250开发者_如何学JAVA426
250640
252399
252555
253610
253949
254641
255109

Sorry for the long list I just want you guys to see the madness. Is there any reason why this would happen? There is another prospect that I know of, 1257506, that has this HIDEORSHOW value and does not appear in the list.

I would lay good money that the HIDEORSHOW field is a string of some kind, and that some values have leading or trailing spaces.

This is true,  due to implicit CASTing:      '1' = 1     (Becomes :  '1' = '1')
This is false, even with implicit CASTing:  ' 1' = 1     (Becomes : ' 1' = '1')

To test this, try this query (or it's equivalent in your version of SQL)...

SELECT PROSPECT_ID FROM tblProspects WHERE CAST(HIDEORSHOW AS INT) = 1;

This will force the casting to be string => int rather than the other way around.

Or you could try this test...

SELECT '<' + HIDEORSHOW + '>', LEN(HIDEORSHOW) FROM tblProspects WHERE PROSPECT_ID = 1261484;

You'll then have more visibility on the exact value in that field.