mysql - function like isnull() to check for zero-value

Question

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.

score 14 · Answer 1 · answered Mar 05 '19 at 21:33

14

Might be useful here: there is a funky way to check if expression is either NULL or 0:

IFNULL(NULLIF(expr, 0), alt_expr)

This returns alt_expr if expr is NULL or 0, otherwise returns expr.

answered Mar 05 '19 at 21:33

Detuner

141
1
4

this is the best answer – Kalpesh Popat Jun 05 '19 at 03:03

score 10 · Answer 2 · answered Jan 02 '15 at 19:33

10

This is shorter and avoids repeating expression:

SELECT COALESCE( NULLIF( expression, 0 ), 'a substitute for zero' ) FROM mytable

answered Jan 02 '15 at 19:33

Nicolas at cividesk

444
4
5

NULLIF is a great idea – Yevgeniy Afanasyev Jun 01 '17 at 02:01

score 3 · Accepted Answer · answered Apr 20 '12 at 13:17

3

SELECT  CASE expression WHEN 0 THEN 'a substitute for zero' ELSE expression END
FROM    mytable

answered Apr 20 '12 at 13:17

Quassnoi

413,100
91
616
614

1

Thank you. That would work. I already thought about ``SELECT IF(expression = 0, 'a substitute for zero', expression)`` which does the same. Both cases are rather looong in my case, though. – simbabque Apr 20 '12 at 13:26

mysql - function like isnull() to check for zero-value

3 Answers3