Solved

TSQL - Replace all NULL with Empty String in a Table

Posted on 2009-07-09
10
2,081 Views
Last Modified: 2012-05-07
Is there a efficient syntax to replace all rows and columns that have NULL with the empty string?

Thank you for your help.
0
Comment
Question by:MisterT25
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:drypz
ID: 24819783
Hi! Do it something like this

      SELECT
            ISNULL(e.EMP_SURNAME, '') AS LastName,
            ISNULL(e.EMP_FNAME, '') AS FirstName,
            ISNULL(e.EMP_MNAME, '') AS MidName
      FROM TABLE_EMPLOYEE e
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24819797
Hello MisterT25,

UPDATE SomeTable
SET Col1 = COALESCE(Col1, ''), Col2 = COALESCE(Col2, ''), Col3 = COALESCE(Col3, '')
WHERE Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL

Regards,

Patrick
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24819884
>>Is there a efficient syntax to replace all rows and columns that have NULL with the empty string?<<
Why would you want to do that?  Do you not like Nulls?
0
 

Author Comment

by:MisterT25
ID: 24820088
I should have explained what I am trying to accomplish.

I am working with an SQL table that will eventually be exported as a Tab delimited text file.  In my testing with the bcp program, it seems that rows that contain Null values will not be exported.  So, if all the Null values were replaced by Empty strings, then bcp would export all the rows rather than skipping some.

I hope this helps.  Thank you.
0
 
LVL 77

Expert Comment

by:arnold
ID: 24820421
update table set col1=isnull(col1,''),col2=isnull(col2,''),....,coln=isnull(coln,'')

You could try the alter table and set a default for each column '',0 based on the column type.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 24820430
Could you post the create table syntax?

alter table tablename
MODIFY (col1 type not null default='',





     coln type not null default=0;)
may work and prevent future NULL entries.
0
 

Author Closing Comment

by:MisterT25
ID: 31601902
Arnold,

I like your idea of setting the default to an empty string.  This will eliminate the need to remove NULLs.

Thank you very much.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24823524
>>In my testing with the bcp program, it seems that rows that contain Null values will not be exported.<<
You need to double check this.
0
 

Author Comment

by:MisterT25
ID: 24823589
acperkins,

Thank you for your feedback.  I have found that bcp will not export all the rows in a table.  I have "guessed" that this was due to having NULL in some rows.  Because bcp does not give an error, I have no way of knowing exactly why it excluded some rows.

I would welcome your opinion on this.  I could open another question for this so you could get the points.   Let me know if you woud like for me to do this.

Thank you very much.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24825647
Not a problem.  If you have found a solution that works for you, that is fine with me.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
c# LinkButton OnClientClick 2 34
Need help debbuging stored procedure 21 34
Split in Javascript 5 31
Sql query 107 27
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now