How can I achieve initcap functionality in MySQL?

by

Sometime back I was looking for a built-in initcap/ucfirst function in MySQL but unfortunately couldn’t find such string functions so decided to write my own.. thanks to the MySQL community member who corrected the bug in my function & posted it back.

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`initcap`$$

CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
SET @str="";
SET @l_str="";
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$

DELIMITER ;


Usage: 

select initcap('This is test string');

More Related Contents:

  1. How to use IN & NOT IN same query [closed]
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. MySQL: Large VARCHAR vs. TEXT?
  4. MySQL: is a SELECT statement case sensitive?
  5. Check overlap of date ranges in MySQL
  6. Multiple-column foreign key in MySQL?
  7. mysql – making a mechanism similar to Oracle’s seqences
  8. Get the latest date from grouped MySQL data
  9. How to execute a MySQL command from a shell script?
  10. UUID performance in MySQL?
  11. Select most common value from a field in MySQL
  12. MySQL: NULL vs “”
  13. MySQL update a joined table
  14. Returning the ‘last’ row of each ‘group by’ in MySQL
  15. Optimal MySQL settings for queries that deliver large amounts of data?
  16. mysql configuration stops at “starting server”
  17. MySQL Error 1264: out of range value for column
  18. Like Case Sensitive in MySQL
  19. What is the best way to delete old rows from MySQL on a rolling basis?
  20. Is it possible to reference one column as multiple foreign keys?
  21. No Entity Framework provider found for ‘MySql.Data.MySqlClient’ ADO.NET provider
  22. mysql equivalent data types
  23. Get difference in years between two dates in MySQL as an integer
  24. How to Import Multiple csv files into a MySQL Database
  25. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  26. Data truncated for column?
  27. Using MySQL triggers to log all table changes to a secondary table
  28. How do you force mysql LIKE to be case sensitive? [duplicate]
  29. Delete all rows with timestamp older than x days
  30. In what order are MySQL JOINs evaluated?

Leave a Comment