I tried one query (originally asked at JR) on MySQL which export the table data into a file, with a comma delimited field. The query is straight forward and replace the null column value by "\N" character, but when I want to replace that "\N" value by some other character like "0" or simple empty field like " ", its gets little tricky.
Here is the base query
And it outputs like,
1, "sagar", 23
2, "anant", 24
Now suppose some 'name' fields are null, then it outputs like
1, "sagar", 23
2, \N, 24
Now to obtain my desired results, which replace this null (\N) values by empty string like, "", I tried out two solutions
OR(By JR mate)
And either of this query gets you the following output into a file
1, "sagar", 23
2, "", 24
Here is the base query
SELECT Id, name, age FROM student INTO OUTFILE 'c:/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
And it outputs like,
1, "sagar", 23
2, "anant", 24
Now suppose some 'name' fields are null, then it outputs like
1, "sagar", 23
2, \N, 24
Now to obtain my desired results, which replace this null (\N) values by empty string like, "", I tried out two solutions
--1. Using CASE statement
SELECT Id,
CASE WHEN name IS NULL THEN ''
ELSE name
END AS NewName,
age
FROM student
INTO OUTFILE 'c:/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
OR(By JR mate)
--2. Using 'ifnull()' function.
SELECT Id, ifnull(name, '') as NewName, age from student;
And either of this query gets you the following output into a file
1, "sagar", 23
2, "", 24
Comments
Thanks !!
It helped .