JOEZACK.COM Code Musings and Such

1. 

   Muhammad Abubakar
   December 29th, 2008 - 04:04

   AOA
   Thanks Friend for this.

   Here is my modified version.

   DELIMITER $$

   DROP FUNCTION IF EXISTS `sharex`.`Capitalize`$$

   CREATE DEFINER=`root`@`%` FUNCTION `Capitalize`(InputString VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
   BEGIN
   DECLARE Input_Length INT;
   DECLARE Count INT;

   SET Input_Length = CHAR_LENGTH(InputString);
   SET InputString = LOWER(InputString);
   SET Count = 0;

   WHILE (Count < Input_Length) DO
   IF (MID(InputString,Count,1) = ‘ ‘ OR MID(InputString,Count,1) = ‘.’ OR Count = 0) THEN
   SET InputString = CONCAT(LEFT(InputString,Count),UPPER(MID(InputString,Count + 1,1)),
   RIGHT(InputString,Input_Length – Count – 1));
   ELSE
   SET InputString = CONCAT(LEFT(InputString,Count),LOWER(MID(InputString,Count + 1,1)),
   RIGHT(InputString,Input_Length – Count – 1));
   END IF;
   SET Count = Count + 1;
   END WHILE;

   RETURN InputString;

   END$$

   DELIMITER ;
2. 

   Ixalmida
   August 28th, 2009 - 10:30

   Awesome function JOEZACK! Works like a charm. Thanks for sharing!
3. 

   alan
   November 25th, 2009 - 19:09

   JOEZACK thanks mate, needed this exact thing and works perfect.

   thanks again
4. 

   Jeremy Woertink
   March 9th, 2010 - 13:09

   DUDE! This layout is awesome. I’m diggin’ the chrome style. Oh, and on a side note, you’re a sick sick individual for writing SQL like that :p lol. Thanks, this is going to help out
5. 

   Jools
   March 26th, 2010 - 13:43

   Hi JOEZACK,

   It’s probably me but when I get down to;
   DECLARE len INT;

   It bombs with a mysql error…

   Have I missed something?
6. 

   Jaime
   April 23rd, 2010 - 16:48

   You are the man, I was testing another functions supposed to do this, and they was failing with non alfabetic characters
7. 

   d3ngar
   September 24th, 2010 - 11:50

   Thanks a lot for this. Really useful…

   I hope you don’t mind if I publish this on my blog too? I will link back to you, of course…
8. 

   Joe Zack
   September 24th, 2010 - 18:40

   No problem, glad I could help!
9. 

   Steven
   November 14th, 2010 - 22:57

   I’m with Jools.. bombs on DECLARE len INT;

   – Nice effort though.
10. 

    Jeff
    November 15th, 2010 - 14:00

    Great script. Worked like a charm!

    For you guys that are getting the mysql error…

    The default delimiter in mysql is shown at the bottom of the sql query window as a semi-colon. You have to change that. Make it a tilde and the script will work perfectly.
11. 

    Вибропогружатель
    April 4th, 2011 - 15:00

    Hallo, Neat Post. Es gibt ein Problem mit Ihrer Website in Internet Explorer, würde dies zu testen … also noch ist der Marktführer und ein großer Teil der Menschen wird Ihre wunderbare schriftlich wegen dieses Problems vermissen.
12. 

    Hunter Monk
    January 13th, 2012 - 14:20

    This works perfectly, thanks!

    It it possible to apply this to an entire column inside a table? In a table, I have a column with a string inside it, and I need to capitalize the first letter of every word.

    Is this possible using your function?

    Thanks,
    Hunter
13. 

    Stefano P
    January 17th, 2012 - 06:27

    Slightly modified version of your code, to capitalize first char only for words longer than X character.
    Una versione leggermente modificata per considerare solo le parole più lunghe di X caratteri.

    DELIMITER $$

    DROP FUNCTION IF EXISTS Capitalize$$

    CREATE FUNCTION Capitalize (InputString VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
    BEGIN
    DECLARE Input_Length INT;
    DECLARE Count INT;
    DECLARE Last_Word INT;
    DECLARE Min_Char INT;

    SET Input_Length = CHAR_LENGTH(InputString);
    SET InputString = LOWER(InputString);
    SET Count = 0;
    SET Last_Word = 0;
    SET Min_Char = 3;

    WHILE (Count Min_Char) THEN
    SET InputString = CONCAT( LEFT(InputString,Last_Word),
    UPPER(MID(InputString,Last_Word + 1,1)),
    RIGHT(InputString,Input_Length – Last_Word – 1));
    END IF;
    SET Last_Word = Count;
    END IF;
    SET Count = Count + 1;
    END WHILE;

    RETURN InputString;
    END$$

    DELIMITER ;

Leave a comment Cancel reply