Removing Null and keep them blank

Question

Post reply

Removing Null and keep them blank

hemalchalishazar

SSC Veteran

Points: 265
More actions
April 1, 2010 at 2:53 pm

#233252

Hi,
I am working on a big table with around 600 columns and 33000 rows.... now for each column there are values in around 500 rows and rest of them NULL.... i want to remove those Nulls and keep them blank (' ')...Can some one help me in the code....?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

DECLARE @test-2 varchar(255);

select ISNULL(@test,'') MyColumn;

edit: I agree with Steve below ... why do you want to do that? Are you wanting to change the data the table contains or do you just want to change how it is displayed?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

I would question why you really want to do that. If you really need to, you can ...

UPDATE MyBigTable SET col1 = '' WHERE col1 is NULL

for each column for that table.

Converting oxygen into carbon dioxide, since 1955.

Tim Weintz SSCommitted Points: 1622 More actions · Answer 3

If you want them to always be non null, you can consider an alter table statement with a "with values' clause in addition to default constraints of '' for each column you are concerned with.

Paul White SSC Guru Points: 150467 More actions · Answer 4

hemalchalishazar (4/1/2010)
I am working on a big table with around 600 columns and 33000 rows.... now for each column there are values in around 500 rows and rest of them NULL.... i want to remove those Nulls and keep them blank.

:w00t: Please do not do this :w00t:

See Using Sparse Columns for a much, much, much better solution.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

hemalchalishazar SSC Veteran Points: 265 More actions · Answer 5

Hi,

Thank you all for your response... The main reason why i would want to go for this removal process is to make my table data look more clean and eventually i can remove certain rows which has no values or all the null values.... i have written a procedure but it is affecting the transaction time efficiency so if i can take some easier way out....

Paul White SSC Guru Points: 150467 More actions · Answer 6

hemalchalishazar (4/2/2010)
Thank you all for your response... The main reason why i would want to go for this removal process is to make my table data look more clean and eventually i can remove certain rows which has no values or all the null values.... i have written a procedure but it is affecting the transaction time efficiency so if i can take some easier way out....

You really don't want to do this. SQL Server can often store NULLs much more efficiently than a string (even a zero-length one). It is likely that you would end up using a lot more storage space for no benefit. What makes you think empty strings are 'neater' than NULLs?

If you are using SQL Server 2008 (this is the forum for 2008), I would encourage you to read the link I posted concerning Sparse Columns.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi