MySQL – Camel Case Function

MySQL – Camel Case Function

Camel Case Function

DELIMITER $$

DROP FUNCTION IF EXISTS `pCase` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `pCase`(str TEXT) RETURNS TEXT CHARSET latin1
    DETERMINISTIC
BEGIN
  DECLARE result TEXT DEFAULT '';
  DECLARE word TEXT DEFAULT '';
  DECLARE working_char TEXT DEFAULT '';
  DECLARE last_space INT DEFAULT 0;
  DECLARE word_boundry TEXT DEFAULT ' (){}[]-/'; #Used to decide when a new word begins.  Add you boundries here!!
  DECLARE ucase_words TEXT DEFAULT 'UK,USA,DVD,DVDs,GB'; #To make some words uppercase use ucase_words below.  The text entered here is used so dvds becomes DVDs.
  DECLARE i INT DEFAULT 1;  #Loop counter
  DECLARE found_boundry INT DEFAULT 1; #When we find a boundry set to 1 (True) else 0 (False)

  # handle NULL
  IF (str IS NULL) THEN
    RETURN NULL;
  END IF;

  # if 0 length string given
  IF (CHAR_LENGTH(str) = 0) THEN
    RETURN '';
  END IF;

  SET str = LOWER(str);

  # loop through each letter looking for a word boundry
  WHILE(i <= (LENGTH(str)+1)) DO

    #Set our working charater
    SET working_char=SUBSTRING(str, i-1, 1);

    #Find a word boundry
    IF(LOCATE(working_char, word_boundry)>0) THEN
      #Check if last word was in our uppercase list, using the example in the list to allow dvds to become DVDs
      IF(LOCATE(word, ucase_words)>0) THEN
        SET result=CONCAT(LEFT(result,(LENGTH(result)-LENGTH(word))),MID(ucase_words,LOCATE(word, ucase_words),LENGTH(word)));
      END IF;

      SET found_boundry=1;  #Set the boundry flag, then ignore
      SET result=CONCAT(result, working_char);
      SET word=''; #Reset word
    ELSE
      SET word=CONCAT(word, working_char);
      IF(found_boundry=1) THEN
        SET result = CONCAT(result, UPPER(working_char));  #After a boundry so upper case
        SET found_boundry=0;
      ELSE
        SET result = CONCAT(result, working_char);
      END IF;

    END IF;

    SET i=i+1;

  END WHILE;

  #Check if last word was in our uppercase list
  IF(LOCATE(word, ucase_words)>0) THEN
    SET result=CONCAT(LEFT(result,(LENGTH(result)-LENGTH(word))),MID(ucase_words,LOCATE(word, ucase_words),LENGTH(word)));
  END IF;

        RETURN result;

END $$

DELIMITER ;

Usage

SELECT pCase(`desired_column`) FROM `t`;

Reference: MySQL Bugs: #2430

Leave a Reply

Your email address will not be published. Required fields are marked *

fourteen + 17 =