SQL: I need to explode a string by ">" and want to remove the last thread and Replace the (-) by space in CamelCase

Question

The string i have is as below and i need the output in camel case as below

For example:
compact-cameras>ixus>digital-ixus-160
compact-cameras>ixus>digital-ixus-160>DSLR
scanners>document-scanners>dr-6030c

Output:
Compact cameras>ixus
Compact Cameras>Ixus>Digital Ixus 160
Scanners>Document Scanner

Answer 1

You can remove last part of string and remove "-" by below statement and then use camel case function as provided link-- camel case function

SELECT REPLACE(REPLACE('compact-cameras>ixus>digital-ixus-160>DSLR',SUBSTRING_INDEX('compact-cameras>ixus>digital-ixus-160>DSLR','>',-1),''),'-',' ');

Is there a simple way to convert MySQL data into Title Case?

Answer 2

I tried with this and works fine for me

DROP FUNCTION IF EXISTS UC_Words; 
    DELIMITER ||  

    CREATE FUNCTION `UC_Words`( str VARCHAR(255) ) RETURNS VARCHAR(255)   
    BEGIN  
      DECLARE c CHAR(1);  
      DECLARE s VARCHAR(255);  
      DECLARE i INT DEFAULT 1;  
      DECLARE bool INT DEFAULT 1;  
      DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:>?/';  
      SET s = LCASE( str );  
      WHILE i < LENGTH( str ) DO  
         BEGIN  
           SET c = SUBSTRING( s, i, 1 );  
           IF LOCATE( c, punct ) > 0 THEN  
            SET bool = 1;  
          ELSEIF bool=1 THEN  
            BEGIN  
              IF c >= 'a' AND c <= 'z' THEN  
                 BEGIN  
                   SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));  
                   SET bool = 0;  
                 END;  
               ELSEIF c >= '0' AND c <= '9' THEN  
                SET bool = 0;  
              END IF;  
            END;  
          END IF;  
          SET i = i+1;  
        END;  
      END WHILE;  
      RETURN s;  
    END ||  

    DELIMITER ; 
    SELECT UC_Words(REPLACE(SUBSTRING_INDEX('compact-cameras>ixus>digital-ixus-160>xyz>pqr', '>', LENGTH(SUBSTRING_INDEX('compact-cameras>ixus>digital-ixus-160>xyz>pqr>khanasim', '>',-1))+1), '-',' '));