I'm realizing that my blog is redundant now that I'm using Google+. I recommend if you are following my blog, please go to my Google+ profile and follow me there.
https://plus.google.com/109926173332943888466
I may come back to the blog, but for now it just doesn't make sense to use both.
I should note that I will be keeping the blog up for as long as Google/Blogger allows, there is a lot of history on this blog and I'd hate to just throw it away.
Thursday, September 29, 2011
Tuesday, September 06, 2011
Removing a NULL from the Results Grid
Here is my scenario. (Yours may very, but when I tried to find help on this issue I found the specifics difficult to locate.)
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:
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.
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.
Subscribe to:
Posts (Atom)