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
Wednesday, June 17, 2009
Subscribe to:
Post Comments (Atom)
10 comments:
I remembered the day you have written this procedure!!! :)
BTW, it was you who requested & forced me to write this code..Thanks..
Thank you - other solutions out there didn't work with multiple word input fields (e.g. city names) - this does. - Jonathan
hi, how about for more than 2 words?
>>hi, how about for more than 2 words?
It works... take a look at the test cases after the article.
Regards,
Umesh
Can you create a function in mysql that imitates the functionality of DECODE in Oracle
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
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
^^ 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)
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