CODEWITHSUNDEEP
mysqlstring
OctaveParango
OctaveParango

Reputation: 331

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 |

Upvotes: 0

Views: 48

Answers (2)

Drew
Drew

Reputation: 24959

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      |
+----+-----------------+

Upvotes: 1

PaulF
PaulF

Reputation: 6773

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

Upvotes: 1

Related Questions