Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL - Replace all NULL with Empty String in a Table

Posted on 2009-07-09
10
Medium Priority
?
2,125 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:Tom Sage
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 93

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Tom Sage
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 79

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
 
LVL 79

Accepted Solution

by:
arnold earned 2000 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:Tom Sage
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:Tom Sage
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

688 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