MySQL - Function to Capitalize First Letter of a Word

October 15, 2015 - I was bored so I went to play around with MySQL and solve some simple problem or exercise below to energize my playful mind.

Write a function to capitalize the first letter of a word in a given string.

Example: initcap(UNITeD states Of AmERIca ) = United States Of America

CREATE TABLE names (name VARCHAR(250));

INSERT INTO names (name) VALUES

  ("uNited states oF americA");


Solution:

-- First word capitalize
CREATE FUNCTION initcap(input VARCHAR(250))
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN

  SET @inputCap = CONCAT(UCASE(LEFT(input, 1)), LCASE(SUBSTRING(input, 2)));
  SET @output = UCASE(LEFT(input, 1));
  SET @index=1;
  SET @previousChar = SUBSTRING(input, @index, 1);
  SET @currentChar = '';

  charLoop: LOOP
    SET @index = @index + 1;
    SET @currentChar = SUBSTRING(input, @index, 1);

    IF @previousChar = ' ' THEN
      SET @output = CONCAT(@output,UCASE(@currentChar));
    ELSE
      SET @output = CONCAT(@output,LCASE(@currentChar));
    END IF;
    SET @previousChar = @currentChar;

    IF (@index < LENGTH(input)) THEN
      ITERATE charLoop;
    END IF;
    LEAVE charLoop;
  END LOOP charLoop;


  RETURN @output;
END//

select initcap(name) from names;

I may be wrong but hopefully I got this right. Feel free to comment or let me know your solutions.