where not in ( int value and null)

statusfk is supposed to be null or have an int value that is 8 numbers. but i saw in the table there is one with 7 digits.

but the following query
select * from tblsinger where len(statusfk) not in (8, null)
did not bring back the records for statusfk with 7 length.

what is wrong with the query?

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
NOT IN + NULL = bad query

NULL is not comparable to anything, so if it is present in the NOT IN clause at all, it is as good as filtering everything out.
0
 
AriMcConnect With a Mentor Commented:
LEN function works on character fields only. For integers you could try:

select * from tblsinger where statusfk between (1000000 and 9999999)

0
 
LowfatspreadConnect With a Mentor Commented:
1 you can only test a column against null with the IS NULL/IS NOT NULL SYNTAX
2  a null value "doesn't" have a length
3 len is for character columns only

so try

select * from tblsinger
 where statusfk < 10000000
 
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
anushahannaAuthor Commented:
>>NOT IN + NULL = bad query
>>you can only test a column against null with the IS NULL/IS NOT NULL SYNTAX

THANK YOU....

now,
select * from tblsinger where statusfk is not null and len(statusfk)<8
worked
(len does work for int - can you please test that)
0
 
SharathConnect With a Mentor Data EngineerCommented:
Yes, you can check the no. of digits with LEN.
0
 
LowfatspreadConnect With a Mentor Commented:
you can "check" with len on the size of the converted string... from the underlying numeric data type

if the statusfk is indexed then the len(statusfk) check makes it non sargable  (less likely to use an index efficiently)

where as the numeric range condition does not suffer from that problem...

in the general case you may want to consider the effect of negative numbers as well
0
 
cyberkiwiConnect With a Mentor Commented:
@AriMC "LEN function works on character fields only. For integers you could try:"
@Lowfatspread

Try select LEN(1234), LEN(1234222)

> have an int value that is 8 numbers

So Len( ) does work to flush it out.

@Lowfatspread

While you are right about performance, the question is worded as a one-off so by the time you posted that tidbit, the problem has been solved...
0
 
anushahannaAuthor Commented:
thanks for your helpful explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.