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?
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.