Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

null and 'blank' - how to make all null 1

Status
Not open for further replies.

micang

Technical User
Joined
Aug 9, 2006
Messages
626
Location
US
Hi All,

SQL 2000:

I have columns in a table that have nulls and 'blanks', is there an easy way of making them all null?

It baffles me how there can be this, as if a field is empty, should it not be null?

many thanks

micanguk
 
thanks sqlsister just to make sure, is that " or '' ?
 
My previous post looks silly!

Sorry, I mean is it " (quotation mark) or two ' (apostrophe)
 
To be sure the column is blank:
Code:
Update table1
set field1 = null
where Len(LTrim(RTrim(field1)) = 0

It baffles me how there can be this, as if a field is empty, should it not be null?

Think of NULL as meaning nothing. In other words, there is NOTHING in the column. A blank, or space actually has a value.

Jim
 
thanks guys for clearing that up.

much appreciated.

micanguk
 
People often confuse NULL with empty string, and it's a shame because NULL does have meaning.

It often helps to think of NULL as 'I don't know'.

For example, suppose you are storing names in a table, with the parts seperated, like this...

Create Table People
(FirstName VarChar(50),
MiddleName VarChar(50),
LastName VarChar(50))

We all know that some people don't have a middle name. It just doesn't exist. In the database, this should be represented by an empty string. If we don't know what someone's middle name is, then it should be represented with a NULL. This allows us to write some reports that would otherwise be unavailable to us.

If we wanted to know those people that we 'don't know' what their middle name is:
Select * From People Where MiddleName Is NULL

If we wanted to know those people that don't have a middle name:
Select * From People Where MiddleName = ''

Middle name may have been a poor choice for an example, but hopefully the point has gotten across. I've seen developers the hate NULL. They usually set a default on EVERY column just to avoid nulls. For example, you may want to track people's weight. Setting a default of 0 for this field would be bad because if you start writing statistical reports, your numbers will be flawed unless you write special code to handle.

By the way... it's 2 apostrophe's in T-SQL to represent an empty string.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Thanks so much for the info George, I now see how null is in fact important and indeed can be used in reports.

Thankns for clearing up the apostrophe's too.

Regards,
Michael
 
If you do a count on a column and there are nulls, will they be counted? or will you always get 0 for it?
 
Nulls aren't included in a count. You can run this query to get the number of rows, number of rows with a value and the number of rows with NULL in a particular field.

Code:
select count(*), count([i]fieldname[/i]) , count(*) - count([i]fieldname[/i]) from table
 
Thanks. pmegan, I see how that works, fantastic stuff.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top