Convert Null values to empty String 3

[Keyth]

Hiya.

How can I set the default value in SQL SVR 2005 bit field to False? And the same goes for nvarchar fields set to empty string?

Is there a statement that can be run to convert all null values in nvarchar field to emtpy strings?
Is there a statement that can be run to convert all null values in a bit field to false?

Thanks,

Keith

 

[nickdel]

Keith, set default value to "0" (zero) for bit field to represent false.

update [table]
set [nvarcharfieldname] = ''
where [nvarcharfieldname] is null

Cheers

Nick

 

[Keyth]

So the defaults only work for new rows, not when adding new columns to existing tables?

 

[gmmastros]

>> How can I set the default value in SQL SVR 2005 bit field to False? And the same goes for nvarchar fields set to empty string?

First, update the data in the table as nickdel suggests. Then, you can add the defaults like this...

ALTER TABLE [!]TableName[/!] ADD CONSTRAINT
	[!]ConstraintName[/!] DEFAULT 0 FOR [!]bit_column_name[/!]

Do that same for the nvarchar column.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nickdel]

Sorry, yeh I wasn't too clear on that. Default will apply to new rows only but as George has pointed out you should update the current data first.

Nick

 

[SQLSister]

And default will not apply if the insert stament specifically sends null (assuming the column allows nulls) or empty string as the value. That is because they are appropriate values. A default works best if the field does not allow nulls. If empty string is being sent inthe insert and you do not want that to be the value, a default will not fix this. In this case you need to fix your insert process or add a trigger to fix the empty string.

"NOTHING is more important in a database than integrity." ESquared

 

[Keyth]

Thanks vry much for your replies

So I set the default Empty String value in the column property pages within SQL SVR by typing in '' ?? If I do, once the value is validated, it gets changed to ('')

How about this though heres a tester for you !! ...

How could you update each field null value with the default? What I mean is, can you replace every null value in the table without running a query for each column? Can you loop through the columns and use the default value to replace any nulls?

Thanks,

Keith

 

[Keyth]

Good point SQLSister. I'm working with old tables but any new ones I have created are all set to not allow nulls.

Thanks again

 

[gmmastros]

Try running this query...

Select 'Update [' + Table_Name + '] Set [' + Column_Name + '] = ' + Column_Default + ' Where [' + Column_Name + '] Is NULL'
From   Information_Schema.Columns 
Where  Column_Default Is Not NULL

When you do, there will be 1 column in your output window. You should be able to copy/paste the column to a new Query Analyzer window. Then, double check each statement and run it if you want.

Essentially, the only thing this query does for you is to save on a LOT of typing.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Keyth]

Well thats excellent, I ran the query and it does output the statements to use seperately, I like it! Like you say half the battles won coz it saves a lot of typing.

Thanks very much, I will give that a try when I have time.