Case delimited String Manipulation MySQL

Question

Is there a way to reprocess entries in MySQL as shown below? (Putting a space before a capitalized character) I can't think of an answer to this, and it seems SQL isn't really built for this kind of stuff. Any ideas appreciated!!

Example Input:
| id | String |
| 1 | FannieMae |
| 2 | FreddyMac |
| 3 |ArthurAndersen|

Example Output:

| id | String |
| 1 | Fannie Mae |
| 2 | Freddy Mac |
| 3 |Arthur Andersen |

I do not think it is possible in MySQL to simply do this, since you would need a kind of replace function that could use a regular expression for finding substrings to replace and MySQL does not have this functionality. Obviously, you can embed as many `replace()` calls into each other as you want to achieve the expected outcome, however this is quite tedious exercise. — Shadow, Aug 22 '16 at 15:43
good point. I could nest 26 replace statements as replace(string, 'A', ' A') for each letter of the alphabet .. sounds like my only option :) — OctaveParango, Aug 22 '16 at 15:55
Or https://stackoverflow.com/questions/25938516/mysql-udf-that-does-a-regex-search-and-replace — Wiktor Stribiżew, Aug 22 '16 at 16:02
If you don't want to add the space before the first capital - then you would need to trim it after the replace. — PaulF, Aug 22 '16 at 16:08
If you found/had a generic regex replacement function it would look something like this: `regexp_replace(s, '([a-z])([A-Z)]', '\1 \2')` — shawnt00, Aug 22 '16 at 16:50

score 1 · Accepted Answer · answered Aug 22 '16 at 16:40

This function would do what you want :

DELIMITER $$

CREATE FUNCTION `InsertSpace`(s1 VARCHAR(1000))     
RETURNS varchar(1000)
BEGIN
  DECLARE rs VARCHAR(1000);
  DECLARE ch BINARY;
  DECLARE i int;
  SET rs = SUBSTRING(s1,1,1);
  SET i = 2;
  label1: LOOP
    SET ch = SUBSTRING(s1,i,1);
    IF ((ch >= 'A') AND (ch <= 'Z')) THEN
      SET rs = CONCAT(rs, ' ');
    END IF;
    SET rs = CONCAT(rs, ch);
    SET i = i + 1;
    IF i > LENGTH(s1) THEN
      LEAVE label1;
    END IF;
  END LOOP label1;
  RETURN rs;
END

Note the declaration of ch as BINARY - to force case sensitive comparisons.

Then use

UPDATE myTable SET `String`=InsertSpace(`String`);

woops forgot to mention, without using functions. However thanks for your time!! — OctaveParango, Aug 22 '16 at 17:40

score 1 · Answer 2 · answered Aug 22 '16 at 17:30

create table stuckTogether
(   id int auto_increment primary key,
    String varchar(200) not null
);
insert stuckTogether(String) values
('FannieMae'),
('FreddyMac'),
('ArthurAndersen'),
('DJMurphyZ');

Function:

DROP FUNCTION IF EXISTS separateStuck;
DELIMITER $$
CREATE FUNCTION separateStuck
(   s VARCHAR(200)
)
RETURNS VARCHAR(200)
BEGIN
    DECLARE sOut VARCHAR(200) DEFAULT '';
    DECLARE iSize,iPos INT;
    SET iSize=LENGTH(s);
    SET iPos=1;

    label1: WHILE iPos<=iSize DO
        SET sOut=CONCAT(sOut,SUBSTRING(s,iPos,1));
        IF ASCII(SUBSTRING(s,iPos,1)) BETWEEN 97 and 122 THEN
            -- it is lowercase
            IF iPos<iSize THEN
                IF ASCII(SUBSTRING(s,iPos+1,1)) BETWEEN 65 and 90 THEN
                    -- the next one is upper case
                    SET sOut=CONCAT(sOut,' ');
                END IF;
            END IF;
        END IF;
        SET iPos=iPos+1;
    END WHILE label1;

    RETURN sOut;
END;$$
DELIMITER ;

Test:

select id,separateStuck(String) as String 
from stuckTogether;

+----+-----------------+
| id | String          |
+----+-----------------+
|  1 | Fannie Mae      |
|  2 | Freddy Mac      |
|  3 | Arthur Andersen |
|  4 | DJMurphy Z      |
+----+-----------------+

woops forgot to mention, without using functions. However thanks for your time!! — OctaveParango, Aug 22 '16 at 17:40

Case delimited String Manipulation MySQL

2 Answers2