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 想要提取与<代码> 日期Col中某一数字表示相对应的数据集。问题是,如果一个Im 查询是Coltext/code>的最后条目,则LOCATE(见评论)回报。 0. 但是,这打破了我的<代码>SUBSTRING,因此只有上半个条目被退回。

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.

Answer 1

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

Answer 2

这里可以有所助益:如果表达是全国人民联盟的或<>>,就会有很强的检查方式。 0:

IFNULL(NULLIF(expr, 0), alt_expr)

alt_expr,expr为NUL或0,否则返回expr。

Answer 3

缩短时间,避免重复表达:

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

友情链接