Mysql - split result when camelcase is found

Question

I have my table with with one column called name, containing names of people in camelCase format, but I need to select those names separate by one space when a capital letter is found: for example, my table would be like this:

|name          |
----------------
| JosephJackson|
|AidenChase    |
|LukeBenjamin  |

but I want to get in this way:

 |name          |
 ----------------
 | Joseph Jackson|
 |Aiden Chase    |
 |Luke Benjamin  |

How do I should use my SELECT? thanks

Answer 1

BEGIN
SET @text = word;
SET @result = "";
SET @i = 1;
WHILE @i <= LENGTH(@text) DO
SET @t = SUBSTRING(@text, @i, 1);
IF @i > 1 AND ASCII(SUBSTRING(@text, @i, 1)) BETWEEN 65 AND 90  OR @t ='_' OR @t = '-' THEN
IF @t <> '_' AND @t <> '-' THEN
SET @result = CONCAT(@result,' ');
END IF;
END IF;
IF @t <> '_' AND @t <> '-' THEN
SET @result = CONCAT(@result , @t);
end IF;
SET @i = @i + 1;
END WHILE;
RETURN @result;
END

The above sql function will split your text from the camel case and also from special characters like underscore(_) and dash(-) if they are used in that text.

Answer 2

I adapted @Mahesh's answer for what I was trying to accomplish (personal preferences):

1. make the first character uppercase
2. make the - and _ characters spaces
3. handle abbreviations
4. replace minor words with lowercase

Here's a comparison of the two.

Mine:

SELECT camelCaseToSpaced('iWasUsingThisForSetting-name-conversionForTheACMECo_project');

outputs...

I Was Using This for Setting name conversion for the ACME Co project

---

Mahesh's version

SELECT camelCaseToSpacedMahesh('iWasUsingThisForSetting-name-conversionForTheACMECo_project');

outputs...

i Was Using This For Settingnameconversion For The A C M E Coproject

---

So here's my code:

CREATE FUNCTION camelCaseToSpaced(string VARCHAR(100)) RETURNS VARCHAR(255)
  BEGIN
    SET @text = string;
    SET @result = "";
    SET @i = 1;
    SET @caps = 0;

    WHILE @i <= LENGTH(@text) DO
      SET @t = SUBSTRING(@text, @i, 1);
      SET @lastCaps = @caps;
      SET @caps = 0;
      -- uppers or _ or -
      IF @i > 1 AND ASCII(SUBSTRING(@text, @i, 1)) BETWEEN 65 AND 90 OR @t ='_' OR @t = '-' THEN
        IF NOT (@t ='_' OR @t = '-') THEN
          set @caps = 1;
        ELSE
          set @caps = 0;
        END IF;
        IF @i < LENGTH(@text) THEN
          set @nextAscii = ASCII(SUBSTRING(@text, @i+1, 1));
        END IF;
        SET @sep = ' ';
        -- to not have no space separator we need:
        -- 1. previous char is cap
        -- 2. next ascii is cap
        IF (@lastCaps = 1 AND @nextAscii BETWEEN 65 AND 90) THEN
          SET @sep = '';
        END IF;
        SET @result = CONCAT(@result, @sep);
      END IF;
      -- all characters except _ and -
      IF @t <> '_' AND @t <> '-' THEN
        -- make first character uppercase
        IF @i = 1 THEN
          SET @t = UPPER(@t);
        END IF;
        SET @result = CONCAT(@result , @t);
      end IF;
      SET @i = @i + 1;
    END WHILE;

    SET @result = replace(@result, ' The ', ' the ');
    SET @result = replace(@result, ' As ', ' as ');
    SET @result = replace(@result, ' In ', ' in ');
    SET @result = replace(@result, ' To ', ' to ');
    SET @result = replace(@result, ' On ', ' on ');
    SET @result = replace(@result, ' Of ', ' of ');
    SET @result = replace(@result, ' For ', ' for ');

    RETURN @result;
  END;

Answer 3

I had improved @MaheshYadav code. This will change any string into proper case.

DELIMITER @@
DROP FUNCTION IF EXISTS change_case@@
CREATE FUNCTION change_case (word VARCHAR(15000)) RETURNS VARCHAR(15000)
COMMENT 'Change the case to Proper Case, A-Z (65-90 decimal|ASCII) , 
  but if first char in between a-z (97-122) than change case to upper' DETERMINISTIC
BEGIN
  SET @text = word; -- input str
  SET @result = ""; -- modified str
  SET @i = 2;       -- counter
  -- if first char is in between a-z than change to upper
  IF ASCII(SUBSTRING(@text, 1, 1)) BETWEEN 97 AND 122 THEN 
    SET @text = CONCAT(UPPER(SUBSTRING(@text, 1, 1)), SUBSTRING(@text, 2));
  END IF;
  SET @result = UPPER(SUBSTRING(@text, 1, 1));
  WHILE @i <= LENGTH(@text) DO
    SET @t = SUBSTRING(@text, @i, 1);
    SET @p = SUBSTRING(@text, @i-1, 1);
    -- if curr_char is upper and pre_char is lower then insert space and the char       eg coName   > co Name
    IF ASCII(@t) BETWEEN 65 AND 90 AND ASCII(@p) BETWEEN 97 AND 122 THEN
    -- eg SomeCO   > Some CO
        SET @result = CONCAT(@result,' ', @t);
    -- pre_char is space & curr_char is lower
    ELSEIF (ASCII(@p) = 32 OR @p = '_' OR @p = '-') AND ASCII(@t) BETWEEN 97 AND 122 THEN
    -- eg some cO  > some CO
      SET @result = CONCAT(@result,' ', UPPER(@t));
    ELSEIF @t = '_' OR @t = '-' THEN
    -- Replace _ OR - > space
      SET @result = CONCAT(@result,' ');
    -- for lower case
    ELSE
    -- someco        > someco
      SET @result = CONCAT(@result, @t);
    END IF;
    SET @i = @i + 1;
  END WHILE;
  SET @result = REGEXP_REPLACE(@result, '[ ]+', ' ');
  RETURN @result;
END @@

DELIMITER ;

---

Example use:

SET @val = 'someVillage';
SELECT @val, change_case(@val) `mod`;

SET @val = 'some village';
SELECT @val, change_case(@val) `mod`;

SET @val = 'some_village';
SELECT @val, change_case(@val) `mod`;