Filtering by renamed column in mysql

I have the following query:

SELECT 
IFNULL(CHAR_LENGTH(fiedl1), 0) +
IFNULL(CHAR_LENGTH(fiedl2), 0) +
IFNULL(CHAR_LENGTH(fiedl3), 0) +
IFNULL(CHAR_LENGTH(fiedl4), 0) +
IFNULL(CHAR_LENGTH(fiedl5), 0) +
IFNULL(CHAR_LENGTH(fiedl6), 0) AS L
FROM mytable;

I only want the records that L are smaller than 100. If I use WHERE clause then I get "Error Code: 1054. Unknown column 'L' in 'where 开发者_开发技巧clause'"

Use a HAVING clause:

SELECT 
IFNULL(CHAR_LENGTH(fiedl1), 0) +
IFNULL(CHAR_LENGTH(fiedl2), 0) +
IFNULL(CHAR_LENGTH(fiedl3), 0) +
IFNULL(CHAR_LENGTH(fiedl4), 0) +
IFNULL(CHAR_LENGTH(fiedl5), 0) +
IFNULL(CHAR_LENGTH(fiedl6), 0) AS L
FROM mytable
WHERE field1 = "yes"
HAVING L < 100;

Try using a derived table:

SELECT L FROM 
(
    SELECT  
    IFNULL(CHAR_LENGTH(fiedl1), 0) + 
    IFNULL(CHAR_LENGTH(fiedl2), 0) + 
    IFNULL(CHAR_LENGTH(fiedl3), 0) + 
    IFNULL(CHAR_LENGTH(fiedl4), 0) + 
    IFNULL(CHAR_LENGTH(fiedl5), 0) + 
    IFNULL(CHAR_LENGTH(fiedl6), 0) AS L 
    FROM mytable
)  AS R
WHERE L < 100;