Trying to count the number of character in a string

by Raisen   Last Updated August 14, 2019 01:06 AM - source

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') => 2 // correct
find_str('Hi there', ' ') => null // incorrect, it's supposed to be 1
find_str('Hi there', space(1)) => null // also incorrect, it's supposed to be 1
Tags : mysql


Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM


getting innodb buffer usage

Updated August 24, 2018 15:06 PM