user3878398 user3878398 - 3 months ago 15
MySQL Question

Case delimited String Manipulation MySQL

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 |

Answer

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`);
Comments