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!

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

Null 2

Status
Not open for further replies.
drallabs

drallabs

Technical User
Mar 2, 2006
28
US
I just received a database full of tables from an outside source. These tables have multiple fields in each of them. My question is this database allowed nulls in all the fields. Is there a way/script that I can run that would automatically convert the nulls to not nulls. I would like to avoid having to manually convert all this data.
 
Sort by date Sort by votes
Can you live with a little copying and pasting?

Code: 
Select 'Update [' + C.Table_Name + '] Set [' + Column_Name + '] = ' + Case When Data_Type Like '%char%' Then '''''' Else '0' End + ' Where [' + Column_Name + '] Is Null' As [Query To Run]
From   Information_Schema.columns As C
       Inner Join Information_Schema.tables As T
         On C.Table_Name = T.Table_Name
Where  Is_Nullable = 'yes'
       And T.Table_Type = 'Base Table'
Order By C.Table_Name

Run that query (In Query Analyzer or SQL Server Management Studio). You will get a result set back. You can copy/paste the column of (Query To Run) in to a new query window and then run that. Before you do that, though, you should eyeball all of the update queries to verify what it is about to do, and also make sure you have a good backup before running this.

To the best of my knowledge, there is no single 'thing' you can do to replace all the nulls in all the columns of all the tables automagically for you. This is probably the best you're gonna get.

Hope it helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I can set my fields in the database to not allow nulls into a field, it will put an empty string instead, therefore i have one bland value. I am not concerned with the difference between a null and empty string. all i want to know is does it have a value or not. having an empty string makes searching for non-existant values easier and less error prone. we have users who may delete a value in a cell, which becomes an empty string. so i would like to replace the nulls with empty strings to make it consistent.
 
Upvote 0 Downvote
a little healthy debate is good as well. I use a SQL Server database and in their help, they dont recommend allowing nulls. So it really seems that it depends on individual workflow requirements.
 
Upvote 0 Downvote
So it really seems that it depends on individual workflow requirements.
Click to expand...
I guess that is a valid argument if each field that you insert a default value into (i.e. a blank string or a zero, rather than a Null), cannot contain that default value as a legitimate entry. Personally, that situation has never arisen for me so I've always needed to allow nulls.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Upvote 0 Downvote
Unless I'm completely missing something, nobody has pointed out the problem with TysonLPrice's statement about the blood bank:

"You would not want a blood bank to match a sample with Null blood type (not yet entered) to a patient with Null blood type (not yet identified) "

NULLS <> NULLS so a join would not match these values at all. That is part of the purpose of the NULLS. If you however set these values to a non-null default value, say an empty string, the join would succeed and you would get the matching rows resulting in an obvious disaster at the blood bank. The only way you match on NULLS is to explicitly say in a query "where columnValue IS NULL".

So while it seems to me Tyson's argument was to point out the flaws in using NULLS, it actually proves how valuable they are.

While I absolutely hate dealing with them on the programming side, I think they are a necessary evil in many cases.

However, I will say that many times DBAs are lazy and allow everything to be NULL when it really should be a required value. For example, an Item.Qty field in an ordersItems table should probably always have some numeric value since an ordered item with no quantity makes no sense. Yet I've worked with many databases that allow this. This throws the onus on the programmer to handle NULLs in fields that he really should not have to worry about. It's those situations that give NULLs a bad wrap I think. In the end, you have to assume everything may be NULL and code for it when reading the data in initially to prevent run-time exceptions.

If I've misunderstood Tyson's example hopefully someone will point that out. I felt it was important to post this so no one would leave this thread thinking NULLs = NULLs in a join.

J

 
Upvote 0 Downvote
your analysis of the blood bank scenario is spot on

i for one would not want to receive blood from someone whose blood type is an empty string, regardless of whether a bright programmer "translated" a NULL into the empty string or whether the empty string was actually stored in the table

NULLs have a purpose, and to try to do away with them because you don't understand or are unwilling to accept that purpose is, in my humble opinion, naïve

r937.com | rudy.ca
 
Upvote 0 Downvote
I love nulls, makes me weed out the flakes during interviews

take this for example

Code: 
create table NullifyTheN00b (id int)
insert NullifyTheN00b values(1)
insert NullifyTheN00b values(2)
insert NullifyTheN00b values(null)

Now I ask what does this return
Code: 
select * from  NullifyTheN00b where id <> 1

more than hal of the people get it wrong

more fun stuff here

NULL trouble in SQL server land

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
>>NULLS <> NULLS so a join would not match these values at all. That is part of the purpose of the NULLS. If you however set these values to a non-null default value, say an empty string, the join would succeed and you would get the matching rows resulting in an obvious disaster at the blood bank. The only way you match on NULLS is to explicitly say in a query "where columnValue IS NULL".

that depends on ansi_null settings of course, if you have a bonehead programmer setting it to OFF before the query then NULL = NULL would match

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
I think (can someone please verify for me) that Set ANSI_NULLS ONLY affects the where clause comparison of data and NOT the joins.

Code: 
[COLOR=green]-- Create a couple test tables.
[/color][COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #T1 (Data [COLOR=blue]VarChar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] #T1 [COLOR=blue]Values[/color]([COLOR=red]'a'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] #T1 [COLOR=blue]Values[/color](NULL)

[COLOR=blue]Create[/color] [COLOR=blue]table[/color] #T2 (Data [COLOR=blue]VarChar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] #T2 [COLOR=blue]Values[/color]([COLOR=red]'b'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] #T2 [COLOR=blue]Values[/color](NULL)

[COLOR=green]-- Default SQL Server behavior
[/color][COLOR=blue]Set[/color] ANSI_NULLS [COLOR=blue]ON[/color]

[COLOR=green]-- No records because NULL <> NULL
[/color][COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #t1 [COLOR=blue]where[/color] data = null

[COLOR=green]-- No records here either.
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   #T1 [COLOR=blue]As[/color] T1
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] #T2 [COLOR=blue]As[/color] T2
         [COLOR=blue]On[/color] T1.Data = T2.Data

[COLOR=green]-- Modify the default behavior
[/color][COLOR=blue]Set[/color] ANSI_NULLS [COLOR=blue]Off[/color]

[COLOR=green]-- ANSI_NULL seeting allows NULL = NULL in where clause
[/color][COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #t1 [COLOR=blue]where[/color] data = null

[COLOR=green]-- Still no records???
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   #T1 [COLOR=blue]As[/color] T1
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] #T2 [COLOR=blue]As[/color] T2
         [COLOR=blue]On[/color] T1.Data = T2.Data

[COLOR=blue]Set[/color] ANSI_NULLS [COLOR=blue]on[/color]
[COLOR=blue]Drop[/color] [COLOR=blue]table[/color] #t1, #T2

Here's my test. Does anyone see anything wrong with it?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
If you want to live without NULLs you should do it by the presence or absence of a row in a table, not by converting the null to some other value that means "unknown," "default," or "to be determined.
 
Upvote 0 Downvote
that is correct -- to eliminate NULLs means to normalize to 5NF (or is it 6NF? i can never remember)

i've never actually done it because it just ~balloons~ the number of tables you end up with (basically one table per nullable column)

plus, to return any meaningful result set requires LEFT OUTER JOINs of ~ginormous~ complexity, and after you're done, guess what you end up with in the result set?

that's right, NULLs

:) :) :)

r937.com | rudy.ca
 
Upvote 0 Downvote
If you want to live without NULLs you should do it by the presence or absence of a row in a table, not by converting the null to some other value that means "unknown," "default," or "to be determined."

I have to live with how the databases were built before I got here (a claims system). The claim may be there but much of the accompaning data is not. For example if is not a BWC claim the claim number will be null but it is a valid claim. They might not know the gender at the time it is entered so it will be null. That all has to be handled in the application because VB does not like nulls.

I've been following this thread as it progressed. Someone asked me to respond to how I would handle various scenarios. I'm not going to do that. But I do see what the point was.

I also see many of the points made. One of our systems here is an imaging system. No nulls are allowed and it's been around for years without issues.

I came away from this thread with nulls have their place but I don't think, as someone mentioned, that everything should just be defaulted to nulls. Careful up front planning and application/data housing analysis should be done to see where they are applicable.

By the way...I was just kidding when I first posted about the blood types.

 
Upvote 0 Downvote
... VB does not like nulls
Click to expand...
there's your problem right there ;-)


nice followup post, tyson

we rarely get that on these forums

good luck with your app and please post again if you have further questions

r937.com | rudy.ca
 
Upvote 0 Downvote
... VB does not like nulls
Click to expand...
What exactly does that mean? I have programed windows applications using VB.NET as well as ASP.NET applications using VB.NET, and I never had a problem dealing with NULLs.

there's your problem right there
Click to expand...
Are you saying that VB is the problem here, or are you a C# advocate that is going to say it is better than VB?

It's not the language that is the problem, it is the programmer....
 
Upvote 0 Downvote
OK, I see. I was trying to understand what version of VB they meant.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
229
Cpreston
Cpreston
Cpreston
  • Locked
  • Question
Not giving result but NUll
Replies
7
Views
150
Andrzejek
Andrzejek
bmacbmac
  • Locked
  • Question
Inserting XML data into SQLServer returns only NULL values 1
Replies
3
Views
145
Andrzejek
Andrzejek
bestmakis
  • Locked
  • Question
Insert with Null
Replies
3
Views
120
bestmakis
bestmakis
Cpreston
  • Locked
  • Question
Does not give details if a field is NULL
Replies
4
Views
59
bborissov
bborissov

Part and Inventory Search

Sponsor

Back
Top