MS SQL Server - Export Results To File without "NULL"

Question

I am using SQL Server Management Studio to export the results of my query to text. I would like to export the results without the character NULL being printed between delimiters.

For example, instead of:

,NULL, 

I would like to export:

,,

Thanks for your help

Solution 1

Just simply use

ISNULL(someColumn, '')

in our query. This will replace all NULL values with an empty string

Solution 2

It depends on how you export the data. If you use the Import and Export Wizard, the NULL strings do not show up in the result file (tested with SQL Server Management Studio 2014).

Select Tasks -> Export Data... In the dialog where you choose the destination, select "Flat File Destination". You can then write a custom query to select the data.

Comments

[hamel]

Is there an easier way, such as some kind of setting that I can turn nulls off?

[marc_s]

@user1518630: no there is not

[HardCode]

Note that this will only work for character fields. If you use this on a date or numeric field, those rows with a NULL value won't show up in the results, and SQL Server won't complain about the data type mismatch.

[strattonn]

I take SMS's standard query which lists all the fields in a column and use Shift+ALT to add ISNULL to all fields at once. I can also take all the field names and add them as an "AS" with Shift+Alt.

[Code Novice]

Years later... I'm still searching for a solution... and yet I'm still being directed to the same answer... ugh. Now to go into myquery and wrap every field with an IsNull()... yikes - If I could use Oracle's SQL Developer to access SQL Server...

[Jai Jeffryes]

One of my favorite all time tricks is to let SQL write my SQL. I'm adding another response @CodeNovice

[kh42874]

Very good, but a very complicated solution to a problem that shouldn't exist in the first place!

[kh42874]

How will this work for outputting the results of a query (as per the original question) rather than a table?

[Developer]

@kh42874 The process I described should work for outputting the results of a query. This is what I meant by "a custom query". More specifically, in the wizard step "Specify Table Copy or Query" you can select the option "Write a query to specify the data to transfer" and then write your query there.

[Code Novice]

Still coming back to this answer. Still no easy way to remove the never asked for text of "NULL". We get it... it's null now stop putting it in my data please.