My (SQL) WorkLog: MySQL - initcap function

Wednesday, June 17, 2009

MySQL - initcap function

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
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('umesh kumar shastry');

Umesh Kumar Shastry

select initcap('ashutosh s');

Ashutosh S

select initcap('rahul giri');

Rahul Giri

select initcap('alam seraj');

Alam Seraj

select initcap('atul kaushik');

Atul Kaushik


10 comments:

Shankar Reddy S said...

I remembered the day you have written this procedure!!! :)

Umesh Shastry said...

BTW, it was you who requested & forced me to write this code..Thanks..

blogmonster said...

Thank you - other solutions out there didn't work with multiple word input fields (e.g. city names) - this does. - Jonathan

Anonymous said...

hi, how about for more than 2 words?

Umesh Shastry said...

>>hi, how about for more than 2 words?

It works... take a look at the test cases after the article.

Regards,
Umesh

Krishna said...

Can you create a function in mysql that imitates the functionality of DECODE in Oracle

Umesh Shastry said...

Krishna,

You can try something like this..

select CASE 'A'
WHEN 'A' THEN 'Iam A'
WHEN 'B' THEN 'Iam B'
ELSE 'Iam unkown alphabet'
END AS "MySQL_DECODE"
from dual;


Let me know if you want this to be done thru SP..

Regards,
Umesh

Anonymous said...

Hi Cigo.
How about single row function instead of 20rows code:
e.g.
SELECT CONCAT( UCASE( SUBSTR( itemname, 1, 1 ) ) , LCASE(SUBSTR( itemname, 2 ) ) ) AS itemname
FROM item

Umesh Shastry said...

^^ It works great for single word but not for multiple words...

mysql> SELECT CONCAT( UCASE( SUBSTR( "umesh shastry", 1, 1 ) ) , LCASE(SUBSTR( "umesh shastry", 2 ) ) ) AS itemname FROM dual;
+---------------+
| itemname |
+---------------+
| Umesh shastry |
+---------------+
1 row in set (0.00 sec)

Anonymous said...

Was really helpfull. I'm facing at the moment that I want to search for two characters. In detail ' ' and '-' .
I adjusted my function in this way:
---------------
BEGIN
SET @str='' ;
SET @l_str='' ;

WHILE IFNULL(LOCATE('-', x),0)>0 or IFNULL(LOCATE(' ', x),0)>0 DO

IF (IFNULL(LOCATE('-', x),0) < IFNULL(LOCATE(' ', x),0))
THEN
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;
ELSE
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 IF;
END WHILE;
RETURN TRIM(CONCAT(@str, CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END
--------------
But it didn't work. Its resulting in endless loops without any result.
Does somebody has an idea? I'm frustrated.
best,

Post a Comment