Remove "\N"s when doing SELECT INTO OUTFILE

Question

I'm doing a SELECT INTO OUTFILE and it's showing a "\N" for every NULL value. Is there any way for me to make it just be blank instead?

I'm on MySQL.

Melburn

Answer 1

You can use the COALESCE function something like this:

COALESCE(yourfield, '')

Thoroughfare

any alternative if i have to perform SELECT * INTO OUTFILE? – Tachometer

Answer 2

Good question, and as relevant today as it was in 2011. I use a stored procedure for this, wich takes the name of a table as argument. Subsequently, it converts all null-values to empty strings (''), using a prepaired statement and no cursors, for cursors are so non-SQL. I've tested it with a table with some 200 columns and a resulting prepaired statement that's about 15,000 characters long:

CREATE DEFINER=`root`@`localhost` PROCEDURE `ExportFixNull`(
   in tblname tinytext)
BEGIN

set @string=concat(
    "Update ",@tblname," set ",
    (
        select group_concat(column_name,"=ifnull(",column_name,",'')")
        from information_schema.columns 
        where table_name=@tblname
    )
    ,";"
 );

prepare s1 from @string;
execute s1;
drop prepare s1;

In the main SQL-file, there is a statement

SET @@group_concat_max_len = 60000;

that might be crucial.

More details (in Dutch): http://wiki.devliegendebrigade.nl/SELECT_INTO_OUTFILE_%28MySQL%29

Regards, Jeroen Strompf

Nardi