0

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
2

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

0

It must be one of the numerous quirks of MySQL; apparently CHAR_LENGTH(str) returns 0 if str is a function argument with the value of ' '. You probably don't have ERROR_FOR_DIVISION_BY_ZERO enabled, hence NULL output.

May be one of the MySQL experts here can explain the behaviour.

dbfiddle

  • This fiddle explains... The problem source is in "safe datatype" system and implicit data types convertion. Intermediate format is VARCHAR/TEXT, so all trailing spaces are removed. Even if the output datatype is CHAR(x) - fiddle2. And transmit_char(CAST(' 1 ' AS CHAR(3))) will not help... – Akina Aug 14 '19 at 5:28
0

You do not need in:

  1. ROUND - the division result must be integer (ever it would be non-integer, it will be rounded due to output data type)
  2. SELECT - the result may be calculated in RETURN immediately
  3. Compound statement - as a result of (2)
  4. Delimiter reassign - as a result of (3)
DROP FUNCTION IF EXISTS get_chars_count;

CREATE FUNCTION get_chars_count (str_to_search VARCHAR(255), char_search_for VARCHAR(1))
RETURNS INT
DETERMINISTIC
NO SQL
RETURN (CHAR_LENGTH(str_to_search)-CHAR_LENGTH(REPLACE(str_to_search,char_search_for,'')))/CHAR_LENGTH(char_search_for);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.