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
Solved

TSQL - Replace all NULL with Empty String in a Table

Posted on 2009-07-09
10
2,097 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please explain the parts of these 2 LINQ expressions 3 53
C# LINQ ForEach() question 6 50
What are the simplest ways to create a Merge Sort? 3 29
Where is this file? 3 25
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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