Solved

TSQL - Replace all NULL with Empty String in a Table

Posted on 2009-07-09
10
2,068 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 76

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

12 Experts available now in Live!

Get 1:1 Help Now