How to avoid double calculations within case statements of MySQL?

I am wondering if it is possible to avoid a second equal calculation within a CASE statement of MySQL 5.7?

CASE  
  WHEN char_length(cat.DESCRIPTION) > 0 THEN char_length(cat.DESCRIPTION)
  ELSE ''
END AS D_LENGTH

The second char_length seems redundant to me and might be reducing query performance. Is there a way to improve this?

Answer

Since you seem to want to display empty string when the character length of the column be zero, you could try using a TRIM trick here:

SELECT TRIM(LEADING '0' FROM CHAR_LENGTH(cat.DESCRIPTION)) AS D_LENGTH
FROM yourTable cat;

This works because whenever the character length of the description be greater than zero, it would never have any leading zeroes. When that length is actually zero, the call to TRIM above would just strip off the single zero, leaving behind an empty string.

Regarding your current approach, no, there isn’t much you can do directly to avoid the double call to CHAR_LENGTH. But, as shown above, there are ways out which completely avoid the duplication.

Leave a Reply

Your email address will not be published. Required fields are marked *