Improve company productivity with a Business Account.Sign Up

x
?
Solved

replace null with ' ' all at once in a table

Posted on 2011-03-25
4
Medium Priority
?
349 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:Ernesto
  • 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 1332 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 1332 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:Lara F
Lara F earned 668 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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

585 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