Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?

[C.R.Vegelin]

Thanks Paul,
Yes, I've tried IFNULL() to map NULL values to empty strings.
But then I get "1";"2";"";"4";"";"2";"9" in stead of 1;2;;4;;2;9
So building CSV files with 1;2;;4;;2;9 output is not possible ?
Maybe an idea to extend the FIELDS options to enable this ...
Regards, Cor
----- Original Message ----- 
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Saturday, April 01, 2006 6:11 PM
Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?


At 13:29 +0100 4/1/06, C.R.Vegelin wrote:
Hi everyone,

I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9
where NULL values are suppressed in the CSV file.
I tried the following alternatives:
a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By ''
    Lines Terminated By '\r\n' ...
    but this generates output like: 1;2;NULL;4;NULL;2;9
b) Select ... Into Outfile ... Fields Terminated By ';'
    Lines Terminated By '\r\n' ...
    but this generates output like: 1;2;\N;4;\N;2;9

Any idea how to get CSV rows like: 1;2;;4;;2;9  ?
Thanks for your time and effort.
Regards, Cor

You could use IFNULL() to map NULL values to the empty string:

mysql> set @x = null, @y = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select ifnull(@x,''), ifnull(@y,'');
+---------------+---------------+
| ifnull(@x,'') | ifnull(@y,'') |
+---------------+---------------+
|               | 1             |
+---------------+---------------+
1 row in set (0.00 sec)

You'll need to apply this to each column that might contain NULL
values.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]