What is the main Differences between Null value and blank?

[dharam_05]

Can anyone explain me what are the main difference between a null value and blank value in database. It important please help me out. thank you.

[Shanti C]

1.A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).

2.An empty value is a "field-formatted" value with no significant data in it.

3.NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

4.Null has no bounds, it can be used for string, integer, date, etc. fields in a database. Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length. If you have no value for a field, use null, not an empty string.

5.Null is the database's determination of an absense of a value logically, so to speak. You can query like: where FIELD_NAME is NULL

[dickersonka]

Good explanation shanti.

I would also say that null is much easiet to query for than a blank value.

[dharam_05]

Thanks guys but i have one query, if i have some null values and some empty string in the table and if i execute select * from from table. whether it will print fields having null and empty string ? what will be the o/p?

[Shanti C]

select * from tablename;
this statement will print all the columns, not the matter of null or empty..

[dickersonka]

if you need to filter out empty strings

select * from table where length(column) > 0

filter out nulls

select * from table where column is not null

if you need both, just combine

[bwagner]

i use this on a daily basis

SELECT * FROM table WHERE isnull(column,'') = ''

or other variations ....

SELECT * FROM table WHERE not isnull(column,'') = ''
SELECT * FROM table WHERE isnull(column,'') <> ''

[Stålhandske]

Although this is an old thread, I'm adding to it because it appears high in searches on the difference between null and an empty string.

This discussion and many other highly ranked discussions of the topic omit another critical difference: When a field is specified as UNIQUE, there can be multiple records with NO VALUE (i.e. NULL) in that field, but of course there cannot be multiple records with the SAME VALUE (e.g. the empty string) in the field.

That is both a logical and a useful characteristic of NULL, but not a characteristic one would like to discover late in the game.