Posted By

zulugrid on 01/27/10


Tagged

case ucwords proper


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

virendrakryadav


MySQL Proper Case Function


 / Published in: MySQL
 

  1. DROP FUNCTION IF EXISTS proper;
  2. SET GLOBAL log_bin_trust_function_creators=TRUE;
  3. DELIMITER |
  4. CREATE FUNCTION proper( str VARCHAR(128) )
  5. RETURNS VARCHAR(128)
  6. BEGIN
  7. DECLARE c CHAR(1);
  8. DECLARE s VARCHAR(128);
  9. DECLARE i INT DEFAULT 1;
  10. DECLARE bool INT DEFAULT 1;
  11. DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
  12. SET s = LCASE( str );
  13. WHILE i < LENGTH( str ) DO
  14. BEGIN
  15. SET c = SUBSTRING( s, i, 1 );
  16. IF LOCATE( c, punct ) > 0 THEN
  17. SET bool = 1;
  18. ELSEIF bool=1 THEN
  19. BEGIN
  20. IF c >= 'a' AND c <= 'z' THEN
  21. BEGIN
  22. SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
  23. SET bool = 0;
  24. END;
  25. ELSEIF c >= '0' AND c <= '9' THEN
  26. SET bool = 0;
  27. END IF;
  28. END;
  29. END IF;
  30. SET i = i+1;
  31. END;
  32. END WHILE;
  33. RETURN s;
  34. END;
  35. |
  36. DELIMITER ;

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: thatnewguy on July 15, 2010

For the record, this function fails when the last character stands alone, e.g., Public John q

There's an easy workaround, if you don't need padding: trim(proper(concat(myfield, ' ')))

Posted By: baerseven on April 25, 2014

I realize how old this is, but I just came across it today. I believe the problem presented by thatnewguy can be fixed in the WHILE statement

WHILE i

Posted By: baerseven on April 25, 2014

Previous comment cut out when trying to type in an equal sign.

Comparing i to Length(str) needs to be less than or equal to. the way it is written, when i equals the length (the last letter in the string), it exits the function.

You need to login to post a comment.