Null and empty..?

[kenjoswe]

Hi all,

I have a textfile that I import to a table.
I'm not sure I understand the difference between NULL and empty.
Example:

Item OrderNo LengthOrderNo
1 0
2 NULL NULL
3 1027612317 10

'Empty' or blank means that the field has something in it otherwise it should be Null.
I have tried to look at the textfile, with the blank OrderNo (item1), in a editor (UltraEdit) but I can't see anything strange with it. What should I look for?

What I really want is to have the OrderNo field to show NULL when the field is blank or NULL.

/Kent J.

 

[JamesLean]

A NULL value means that the value is unknown or unspecified. An empty string ('') means that there is a known value but that value is empty. There is a definite difference between the two!

As for your text file, do you actually have the string value 'NULL' in it, as per your example? Usually in a text file there is no difference between an empty string and a NULL value as they are both represented visually as "nothing".

--James

 

[kenjoswe]

JamesLean,

Yes, I have seen the explanation in Books online but I don't understand it. How can a known value be empty with the length of 0?

If I look at the textfile for OrderNo in Item2 it looks the same as Item1 - no value. I can't see any difference.
Item1 and Item2 are from different textfiles.
I have also tried to look at the files in Hex but that created more confusion.

NULL is presented to me in Query Analyzer after I have loaded the textfile into the table.

/Kent J.

 

[vongrunt]

NULL is unknown, empty is... empty. The difference is clearly visible in languages that support both values, for example VB:

NULL + 5 = NULL
NULL * 5 = NULL

Empty + 5 = 5
Empty * 5 = 0

What I really want is to have the OrderNo field to show NULL when the field is blank or NULL.

That's where NULLIF() function sometimes becomes handy.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)