I'm backing up MySql -table to worksheet. My current result-set has rows where some of columns are empty.
That's ok, but for the worksheet i need these to be replaced with 'foo'. HowTo?
All help is highly appreciated.
+3
A:
Empty or NULL? There's a big difference there. If it's NULL, you can use the COALESCE() function:
SELECT COALESCE(`MyColumn`, 'foo') As MyColumn FROM `MyTable`
If the value is only empty, you need to do something more like this:
SELECT IF(char_length(`MyColumn`)>0, `MyColumn`, 'foo') AS MyColumn FROM `MyTable
Or you can even combine them:
SELECT IF(char_length(COALESCE(`MyColumn`,''))>0,`MyColumn`,'foo') AS MyColumn FROM `MyTable`
Joel Coehoorn
2009-03-24 14:49:16
for some reason this doesn't do it: SELECT idR, kyy_FK, COALESCE(team,'tag'), COALESCE(name,'xxxxx'), pojong, krd, COALESCE(memo,'foo'), aa FROM rapsa GROUP BY krd, datetime ORDER BY `rap`.`id`i get columns named like: COALESCE(team, 'tag') and empty values on them
Kaptah
2009-03-24 15:13:17
Like I said before: Empty is not the same as NULL. Coalesce requires NULL. You might try using the char_length() function combined with IF instead.
Joel Coehoorn
2009-03-24 15:16:26
sorry, forgot to answer. Empty, not NULL.
Kaptah
2009-03-24 15:18:09
SELECT idR, kyy_FK, IF(char_length(team)=0,'tag',team)...thanks :)
Kaptah
2009-03-24 15:25:10
for some reason this doesn't do it: SELECT idR, kyy_FK, IFNULL(team,'tag'), IFNULL(name,'xxxxx'), pojong, krd, IFNULL(memo,'foo'), aa FROM rapsa GROUP BY krd, datetime ORDER BY `rap`.`id`i get columns named like: IFNULL(team, 'tag') and empty values on them
Kaptah
2009-03-24 15:14:57
+2
A:
The COALESCE statement returns the first non-null parameter that it is passed:
SELECT COALESCE(column,'foo')
Returns column if it isn't null, 'foo' otherwise.
Welbog
2009-03-24 14:49:45