mysql - function like isnull() to check for zero-value
isnull, mysql, substring, zero
Solution
SELECT CASE expression WHEN 0 THEN 'a substitute for zero' ELSE expression END
FROM mytable
Problem
I'm looking for a function to check if a column has the value 0 that behaves like the `isnull()` function. This is what I've got so far: ``` CONCAT( SUBSTRING_INDEX( SUBSTRING(textCol, 1, LOCATE(DATE_FORMAT(dateCol,'%d.%m.%Y %H:%i'), textCol)-1), '|', -1), SUBSTRING( textCol, LOCATE(DATE_FORMAT(dateCol,'%d.%m.%Y %H:%i'), textCol), IFNULL( LOCATE( /* this will return 0 if it's the last entry */ '|', textCol, LOCATE(DATE_FORMAT(dateCol,'%d.%m.%Y %H:%i'), textCol) ) - LOCATE(DATE_FORMAT(dateCol,'%d.%m.%Y %H:%i'), textCol), LENGTH(textCol)))) ``` The data in textCol is sort of like a CSV-file with another CSV-file inside each column. Each dataset is delimited by `|` while each dataset inside that is delimited by `&&`. The inner fields are name, date like `'%d.%m.%Y %H:%i'` and a comment. i want to extract the dataset that corresponds to a certain DATETIME I've got in `dateCol`. The problem is that if the one I'm looking for is the last entry in `textCol`, the `LOCATE` (see comment) returns 0. But that breaks my `SUBSTRING` so only the first half of the entry is returned. I added a `ISNULL()`, which of course doesn't do anything here. What I would like to do is have something similar to the `ISNULL()` that sort of does a `ISZERO()`, much like the `||` operator in Perl behaves. There might be an alltogether better way to achieve what I want. Please don't hesitate to tell me.