INITCAP Function - Oracle to MySQL Migration

In Oracle the INITCAP function capitalizes the first letter of each word and lowercases all other letters in a string. Words are delimited by a space or any other non-alphanumeric letter.

In MySQL you can use a user-define function (UDF) to implement this functionality (see below).

Oracle:

  -- Capitalize the first letter of each word
  SELECT INITCAP('new york') FROM dual;
  # New York

MySQL:

  -- Capitalize the first letter of each word -  you can find the source code of UDF below
  SELECT INITCAP('new york');
  # New York

In MySQL you have to use an user-defined function to implement INITCAP:

MySQL:

  DELIMITER //
 
  CREATE FUNCTION INITCAP (str VARCHAR(8000))
    RETURNS VARCHAR(8000)
  BEGIN
    DECLARE len      INT DEFAULT LENGTH(str);
    DECLARE pos      INT DEFAULT 1;
    DECLARE ch       CHAR(1);
    DECLARE ch_ascii INT;
 
    DECLARE out_str VARCHAR(8000) DEFAULT '';
    DECLARE prev_alphanum INT DEFAULT 0;
 
    WHILE pos <= len 
    DO
      SET ch = SUBSTRING(str, pos, 1);
      SET ch_ascii = ASCII(ch);
 
      IF prev_alphanum = 1 THEN
        SET out_str = CONCAT(RPAD(out_str, pos - 1), LOWER(ch));  -- RPAD is required to append ' ' 
      ELSE 
        SET out_str = CONCAT(RPAD(out_str, pos - 1), UPPER(ch));
      END IF;
 
      IF ch_ascii <= 47 OR (ch_ascii BETWEEN 58 AND 64) OR
	  (ch_ascii BETWEEN 91 AND 96) OR (ch_ascii BETWEEN 123 AND 126) THEN      
	  SET prev_alphanum = 0;
      ELSE
	  SET prev_alphanum = 1;
      END IF;
 
      SET pos = pos + 1;    
    END WHILE;  
 
    RETURN out_str;
  END;
  //
 
  DELIMITER ;

So now you can use this user-defined function in MySQL:

MySQL:

  -- Capitalize the first letter of each word
  SELECT INITCAP('new york');
  # New York

For more information, see Oracle to MySQL Migration.