Trying to count the number of character in a string I've created this MySQL function to count the number of characters (str) in a string(search):

drop function if exists find_str;

delimiter //

CREATE FUNCTION `FIND_STR`(search VARCHAR(255), str CHAR(1))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
BEGIN
SELECT ROUND (   
        (
            CHAR_LENGTH(search)
            - CHAR_LENGTH( REPLACE ( search, str, "") ) 
        ) / CHAR_LENGTH(str)
    ) AS count INTO @count;
return @count;
END//

delimiter ;


If str is space, the function always return null, but I'm not sure why.

find_str('Hello', 'l') =&gt; 2 // correct
find_str('Hi there', ' ') =&gt; null // incorrect, it's supposed to be 1
find_str('Hi there', space(1)) =&gt; null // also incorrect, it's supposed to be 1
I think the problem has to do with the definition of CHAR(..).  It includes trailing blanks to fill out the space indicated.  But the actual string is does not have any trailing spaces.  So ' ' becomes ''.

So, change from str CHAR(1) to str VARCHAR(1).