I had written a query in Microsoft SQL Server Management Studio 2008 to create a data file to be imported via a custom app. The reason for this is that the new data repository requires the data to be input using it's specific tools. There are a number of apps like this.
Prior to working in MS SQL I had begun the project in Access. The project was simpler then and there was a chance that some of my coworkers, who didn't have SQL Server, might need to access it. When exporting my data from Access the NULL values exported as blanks in my spreadsheet. This is exactly what I needed.
After migrating to MS SQL though the NULLs show up as NULL in the spreadsheet. This caused a lot of havoc to the import program. I could have had the programmer change the program to deal with the NULLs, and I may for the next version, we are too far along in the testing phase to change that now.
So, here is the part you have been waiting for, how do you get rid of the NULL values in a SQL export?
Your needs may vary but, use:
ISNULL(column_name,replacement_value)
Where column_name is the name of the data column you are checking and replacement_value is what you want in the field instead of "NULL"In my case my replacement_value was simply: '' (two single quotes) I didn't want any value in the field, not a space, not a "NULL", nothing. While NULL IS nothing to MS SQL, to a CSV file, NULL and '' are the same. Since I am exporting to CSV, I need the '' rather than the NULL.
This may be elementary for some SQL query writers out there, it was important for me to find and use. Unfortunately there was little to point me in the right direction. My hope is that this post will save others who are running into this issue some time.
If you are a SQL guru and there is an even better way to address this problem, please leave a comment below and I'll edit as necessary.
No comments:
Post a Comment