Solved

replace null with ' ' all at once in a table

Posted on 2011-03-25
4
343 Views
Last Modified: 2012-05-11
Hi all
how can i replace null values in a table with ' ' all at once
is any way?
thanks a lot
0
Comment
Question by:edo60
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35217167
UPDATE Table SET Field='' WHERE Field IS NULL
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 333 total points
ID: 35217298
You would have to include all columns that are character data (char, nchar, varchar, nvarchar, text, ntext) and then do an UPDATE as in:
UPDATE YourTable
SET Col1 = CASE WHEN Col1 IS NULL THEN '' ELSE Col1 END,
    Col2 = CASE WHEN Col2 IS NULL THEN '' ELSE Col2 END,
    Col3 = CASE WHEN Col3 IS NULL THEN '' ELSE Col3 END,
    Col4 = CASE WHEN Col4 IS NULL THEN '' ELSE Col4 END,
    ...
WHERE	Col1 IS NULL
	OR Col2 IS NULL
	OR Col3 IS NULL
	OR Col4 IS NULL
          ...

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 333 total points
ID: 35217309
But you should also consider why you are doing that.  For example, I would never allow an app developer bully you into doing that.  It is usually a very bad idea.  They are NULL for a reason.
0
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 167 total points
ID: 35217356
If this is business requirements, After you update columns,  change column definition  to not allow null

alter table tableName alter column ColumnName <you current datattipe> not null
you can also define default for column, which will be inserted when no value send in update


0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question