I was working on a project to export some data into a tab delimited file. Initially, we did some test files and it was simple enough to use a select query and export the results to a text file by right clicking the top left box of the results pane. However, the recipient indicated that the file had NULL columns with the text string 'NULL' in them which interfered with their processing. They requested us to remove the NULL string and instead pass on these fields as blank. I found a free tool called FnR, for Find and Replace, which replace text strings with whatever value is specified. It was all working fine till they asked us to send a full file with more than half a million records. FnR refused to work and would time out. I generated a command line command, another useful feature built into FnR, and tried that, but no joy. So i went to my trusted friend Google and asked for solutions. I found that this has been a very common issue and many people had asked for a solution, only t get lectures on what they are doing wrong and long debates of what DBAs and Analysts deemed best practices. Some workable solutions were also discovered in the process, but when dealing with a large number of fields it becomes impractical to use REPLACE or COALESCE commands with every field to check if it contained a NULL and then replace it with blanks. While the debate lingered on from forum to forum, the person asking the question would eventually bail out due to frustration without getting a proper solution to their problem. I did however came across one suggestion that wasn't given much attention of using the query to export the results to text, rather than the right click process I was following. I decided to give it a try and, viola, no more NULL strings.
