Solved

replace null with ' ' all at once in a table

Posted on 2011-03-25
4
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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