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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.