My (SQL) WorkLog: MySQL - lower,upper char count

Thursday, June 18, 2009

MySQL - lower,upper char count

In one of my task I wanted to get the count of upper/lower char from a string but noticed that the string function provided in MySQL couldn't get me that.. and also at the moment it seems that the regex function in MySQL can match only and the matched stats cannot be captured nor returned.

I know 2 liner code in any scripting language can perform this tasks very well.


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

CREATE FUNCTION `uGetLowerUpperCharCount`(prm_string varchar(250)) RETURNS varchar(250) CHARSET latin1
DECLARE strPos INT default 1;
DECLARE strUpperLen INT default 0;
DECLARE strLowerLen INT default 0;
DECLARE strNonAlphaLen INT default 0;
WHILE strPos <= LENGTH(prm_string) DO IF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 65 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=90 THEN SET strUpperLen = strUpperLen+1; ELSEIF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 97 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=122 THEN SET strLowerLen = strLowerLen+1; ELSE SET strNonAlphaLen = strNonAlphaLen+1; END IF; SET strPos = strPos+1; END WHILE; RETURN CONCAT('String ',prm_string,' has ', IF(strUpperLen>0,CONCAT(strUpperLen, ' - Upper Chars '),''),IF(strLowerLen>0,CONCAT(',',strLowerLen,' - Lower Chars '),''),IF(strNonAlphaLen>0,CONCAT(' and ',strNonAlphaLen,' - Non-Alpha '),''));



select uGetLowerUpperCharCount('Umesh Kumar Shastry');

String Umesh Kumar Shastry has 3 - Upper Chars ,14 - Lower Chars and 2 - Non-Alpha

No comments:

Post a Comment