MySQL substring in function not returning whitespace

Question

I have a function that is for changing text into proper case.

CREATE FUNCTION `fpropercasetest` (p_Value VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE v_i INT; -- counter
DECLARE v_ProperCaseText VARCHAR(5000);
DECLARE v_Word VARCHAR(1000);
DECLARE v_isWhiteSpace TINYINT(1);
DECLARE v_c CHAR(1);

SET v_Word = '';
SET v_i = 1;
SET v_isWhiteSpace = 1;
SET v_ProperCaseText = '';

SET p_Value = LOWER(p_Value);

WHILE (v_i <= LENGTH(p_Value)+1) DO

    SET v_c = SUBSTRING(p_Value,v_i,1);

    IF v_isWhiteSpace = 1 THEN SET v_c = UPPER(v_c); END IF;

    SET v_isWhiteSpace = CASE WHEN (ASCII(v_c) BETWEEN 48 AND 58) THEN 0
                                    WHEN (ASCII(v_c) BETWEEN 64 AND 90) THEN 0
                                    WHEN (ASCII(v_c) BETWEEN 96 AND 123) THEN 0
                                    ELSE 1 END;

    IF v_isWhiteSpace = 0 THEN
        SET v_Word = CONCAT(v_Word, v_c);
    ELSE
        SET v_ProperCaseText = CONCAT(v_ProperCaseText, v_Word, v_c);
        SET v_Word = '';
    END IF;
    SET v_i = v_i + 1;
END WHILE;
return v_ProperCaseText;
END;

You can test with the following

SELECT fpropercasetest("this is just some text for you to test with");

When I entered any text with spaces I noticed that it just didn't register (e.g. with the text "this is some text" it would return "ThisIsSomeText" and it should return "This Is Some Text")

I debugged it with Visual Studio and noticed that substring was just ignoring spaces completely, it returns an empty string.

I'm completely stumped by this, any help will be greatly appreciated. Thanks.

Let me edit the text, it's just the definition for the function missing — triangulito
– triangulito, Commented Aug 5, 2015 at 23:35
I answered the question and tested the code, please make sure to mark it right after you test it. — Hatem Jaber
– Hatem Jaber, Commented Aug 6, 2015 at 0:38
Yeah, just noticed today, let me check it out and I'll get back to you. Thank you! — triangulito
– triangulito, Commented Aug 6, 2015 at 15:40

Hatem Jaber · Accepted Answer · 2015-08-06 00:31:31Z

Sorry about the delay, I had a few things to take care of before leaving work, here is what I came up with, it does what you want it to do:

FUNCTION `upper_all`(p_Value VARCHAR(8000)) RETURNS varchar(8000) CHARSET latin1
BEGIN
DECLARE pos INT; -- counter
DECLARE result VARCHAR(1000);
SET p_Value = LOWER(p_Value);
SET p_Value = CONCAT(UCASE(LEFT(p_Value, 1)),LCASE(SUBSTRING(p_Value, 2)));
SET pos = LOCATE(' ',p_Value);
SET result = SUBSTR(p_Value,1,LOCATE(' ',p_Value));
SET p_Value = SUBSTR(p_Value FROM pos);
WHILE (pos > 0) DO
    /*SET p_Value = CONCAT(p_Value,UCASE(LEFT(p_Value, pos)),LCASE(SUBSTRING(p_Value, 2)));*/

    SET pos = LOCATE(' ',p_Value);
    IF pos > 0 THEN 
        SET pos = pos + 1;
        SET p_Value = SUBSTR(p_Value FROM pos);
    END IF;
    SET p_Value = CONCAT(UCASE(LEFT(p_Value, 1)),LCASE(SUBSTRING(p_Value, 2)));
    SET result = CONCAT(result,SUBSTR(p_Value,1,LOCATE(' ',p_Value)));
END WHILE;
SET result = CONCAT(result,p_Value);
return result;
END

I didn't have time to comment it out for you, it should self-explanatory. It could use a little cleanup and optimize the code a bit.

Jennifer Meyer · Accepted Answer · 2019-09-06 17:27:16Z

create function fn_title_case
    (v_input_string varchar(255))
RETURNS varchar(255)
BEGIN

declare i int;
DECLARE punctuation varchar(17);
declare this_character char(1);
declare output_string varchar(255);
declare boolean_value int;
declare input_length int;

set input_length = char_length(v_input_string);
set boolean_value = 0;
set i = 0;
set punctuation = ' ';
set output_string = '';

begin
-- Loop through each character in the input string
while i <= input_length do
    -- Set the current substring to position i with length 1
    SET this_character = SUBSTRING( v_input_string, i, 1 );
    -- If the current character exists in the punctuation string, i.e. if the current character is a space
    IF LOCATE( this_character, punctuation ) > 0 THEN
        -- Set the current character to null and the boolean to 1
        set this_character = '', output_string = concat(output_string, this_character), boolean_value = 1;
    elseif boolean_value=1 then
        -- Only if the punctuation (a space) was detected in the previous step, 
        -- Insert a space and capitalize the letter
        SET output_string = concat(output_string, ' ', UCASE(this_character)), boolean_value = 0;
    else
        -- If the space was NOT detected, add the current character in lower case
        set output_string = concat(output_string, LCASE(this_character));
    end if;
    set i = i+1;
end while;
end;
    RETURN output_string;

Collectives™ on Stack Overflow

MySQL substring in function not returning whitespace

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related