MySQL. Logical error in function that should return string with spaces before capital letters

Question

I tried recreating an SQL server function on MySQL. The function should be able to return a string with spaces before capital letters. For example, writing "HelloWorld" would return "Hello World".

Other Examples:

"IanLikesJane" -> "Ian Likes Jane"

"CodLiverOil" -> "Cod Liver Oil"

My logic is as follows

result string = ''
Foreach character in input string:

  If this letter is capital:
    If the preceding or next letter is not capital:
      If the the preceding and current letter is not a space:
        Concatinate space after result string

Concatinate this letter with result

MySQL:

DELIMITER $$
CREATE FUNCTION SpaceBeforeCapital (str NVARCHAR(8000)) 
RETURNS NVARCHAR(8000)
DETERMINISTIC
BEGIN
DECLARE i INT; 
DECLARE j INT;
DECLARE cp NCHAR;
DECLARE c0 NCHAR;
DECLARE c1 NCHAR;
DECLARE result NVARCHAR(8000);

SET i = 1;
SET j =  CHAR_LENGTH(str);
SET result = '';    

WHILE (i <= j) DO       
   SET cp = SUBSTRING(str,(i-1),1);
   SET c0 = SUBSTRING(str,i,1);
   SET c1 = SUBSTRING(str,(i+1),1);

   -- IF c0 = UPPER(c0) THEN
   -- IF c0 = UPPER(CONVERT(c0 USING latin1)) AND (cp != UPPER(CONVERT(cp USING latin1)) OR c1 != UPPER(CONVERT(c1 USING latin1))) 
    IF c0 = UPPER(CONVERT(c0 USING latin1)) THEN
        IF cp != UPPER(CONVERT(cp USING latin1)) OR c1 != UPPER(CONVERT(c1 USING latin1)) THEN
            IF cp != ' ' AND c0 != ' ' THEN
                SET result = CONCAT(result,' ');
            END IF;
        END IF;
    END IF;
   -- END IF; 

   SET result = CONCAT(result,c0);
   SET i = i + 1;

END WHILE;

RETURN result;
END$$
DELIMITER;

My script is pretty much doing the opposite the input strings without any spaces. Anyone got a fix?

Can you edit your post and at the bottom, show a sample string of what is expected as the inbound parameter and what you are trying to get out of it... Your verbiage describing doesn't quite make sense. — DRapp
– DRapp, Commented Dec 12, 2013 at 12:46
As I said, the input string "HelloWorld" should return "Hello World" (space before the capital letter). Similar examples include: "IanLikesJane" -> "Ian Likes Jane" "CodLiverOil" -> "Cod Liver Oil" — Aaron Debattista
– Aaron Debattista, Commented Dec 12, 2013 at 12:49
Sorry, my bad on missing Hello World sample... eyes still focusing this morning :) — DRapp
– DRapp, Commented Dec 12, 2013 at 12:51
No worries, the function doesn't return any errors, but it doesn't behave the way it should either. — Aaron Debattista
– Aaron Debattista, Commented Dec 12, 2013 at 12:53

Tom Mac · Accepted Answer · 2013-12-12 13:27:25Z

1

I reckon that the problem here is that case sensitivity is not working as you'd like.

Try replacing UPPER with BINARY UPPER to force case sensitivity. Hence:

DELIMITER $$


CREATE FUNCTION SpaceBeforeCapital (str NVARCHAR(8000)) 
RETURNS NVARCHAR(8000)
DETERMINISTIC
BEGIN
DECLARE i INT; 
DECLARE j INT;
DECLARE cp NCHAR;
DECLARE c0 NCHAR;
DECLARE c1 NCHAR;
DECLARE result NVARCHAR(8000);

SET i = 1;
SET j =  CHAR_LENGTH(str);
SET result = '';    

WHILE (i <= j) DO       
   SET cp = SUBSTRING(str,(i-1),1);
   SET c0 = SUBSTRING(str,i,1);
   SET c1 = SUBSTRING(str,(i+1),1);

   -- IF c0 = UPPER(c0) THEN
   -- IF c0 = UPPER(CONVERT(c0 USING latin1)) AND (cp != UPPER(CONVERT(cp USING latin1)) OR c1 != UPPER(CONVERT(c1 USING latin1))) 
    IF c0 = BINARY UPPER(CONVERT(c0 USING latin1)) THEN
        IF cp != BINARY UPPER(CONVERT(cp USING latin1)) OR c1 != BINARY UPPER(CONVERT(c1 USING latin1)) THEN
            IF cp != ' ' AND c0 != ' ' THEN
                SET result = CONCAT(result,' ');
            END IF;
        END IF;
    END IF;
   -- END IF; 

   SET result = CONCAT(result,c0);
   SET i = i + 1;

END WHILE;

RETURN result;
END$$


DELIMITER ;

answered Dec 12, 2013 at 13:27

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Debattista Over a year ago

Scratch that, it worked :). I removed the Convert also and kept the character on its own.

DRapp · Accepted Answer · 2015-03-06 12:41:29Z

1

If still no luck from others, I have the following two suggestions... Try just using UPPER() without convert / Latin1... as you already have a single character to deal with.

IF c0 = UPPER(c0) THEN

same with rest of upper() conversion tests

If that does not work, I would then build a string at the beginning of your function such as

DECLARE AllUpper NVARCHAR(26);
SET AllUpper = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

then use INSTR() to see if the character is in the all upper string

   IF INSTR( AllUpper, c0 ) > 0

similar with upper conversion tests

edited Mar 6, 2015 at 12:41

answered Dec 12, 2013 at 13:59

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Collectives™ on Stack Overflow

MySQL. Logical error in function that should return string with spaces before capital letters

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related