Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

replace null with ' ' all at once in a table

Posted on 2011-03-25
4
Medium Priority
?
347 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:Larissa T
Larissa T 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

972 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