length:返回字符串所占的字节数,是计算字段的长度。utf8编码下,一个汉字是算三个字符,一个数字或字母算一个字符。其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。
char_length:返回字符串所占的字符数,不管汉字还是数字或者是字母都算是一个字符
DELIMITER $$
CREATE DEFINER=`testuser`@`%` PROCEDURE `getMaxSubStrP`(`inParam1` varchar(100),`inParam2` varchar(100), out `resultStr` VARCHAR(100), out `resultLen` int)
BEGIN
DECLARE longStr VARCHAR(255);
DECLARE shortStr VARCHAR(255);
DECLARE tempStr VARCHAR(255);
DECLARE tempResult VARCHAR(255) DEFAULT "";
DECLARE strLength1 INT DEFAULT 0;
DECLARE strLength2 INT DEFAULT 0;
DECLARE minStrLength INT DEFAULT 0;
DECLARE startIndex INT DEFAULT 0;
DECLARE endIndex INT DEFAULT 1;
DECLARE cmpInt INT DEFAULT 0;
SELECT CHAR_LENGTH(inParam1), CHAR_LENGTH(inParam2) INTO strLength1,strLength2 FROM DUAL;
SET minStrLength = strLength1;
IF minStrLength>strLength2 THEN
SET minStrLength=strLength2;
SET longStr = inParam1;
SET shortStr = inParam2;
ELSE
SET longStr=inParam2;
SET shortStr=inParam1;
end if;
-- select minStrLength,longStr,shortStr;
WHILE startIndex < minStrLength
DO
SET endIndex = startIndex + 1;
WHILE endIndex<=minStrLength
DO
SELECT SUBSTR(shortStr,startIndex+1,endIndex-startIndex) into tempStr from DUAL;
SELECT INSTR(longStr, tempStr ) into cmpInt FROM dual;
-- select startIndex,endIndex,tempStr,tempResult,cmpInt;
IF (cmpInt>0) AND (CHAR_LENGTH(tempResult)<char_length(tempstr)) then THEN
SET tempResult=tempStr;
END if;
SET endIndex = endIndex + 1;
end WHILE; -- endIndex while
set startIndex = startIndex + 1;
end while; -- startIndex WHILE
set resultStr=tempResult;
SELECT CHAR_LENGTH(resultStr) INTO resultLen FROM DUAL;
-- set resultLen= CHAR_LENGTH(resultStr);
-- select @resultLen,@resultStr;
END$$
DELIMITER ;
char_length:返回字符串所占的字符数,不管汉字还是数字或者是字母都算是一个字符
DELIMITER $$
CREATE DEFINER=`testuser`@`%` PROCEDURE `getMaxSubStrP`(`inParam1` varchar(100),`inParam2` varchar(100), out `resultStr` VARCHAR(100), out `resultLen` int)
BEGIN
DECLARE longStr VARCHAR(255);
DECLARE shortStr VARCHAR(255);
DECLARE tempStr VARCHAR(255);
DECLARE tempResult VARCHAR(255) DEFAULT "";
DECLARE strLength1 INT DEFAULT 0;
DECLARE strLength2 INT DEFAULT 0;
DECLARE minStrLength INT DEFAULT 0;
DECLARE startIndex INT DEFAULT 0;
DECLARE endIndex INT DEFAULT 1;
DECLARE cmpInt INT DEFAULT 0;
SELECT CHAR_LENGTH(inParam1), CHAR_LENGTH(inParam2) INTO strLength1,strLength2 FROM DUAL;
SET minStrLength = strLength1;
IF minStrLength>strLength2 THEN
SET minStrLength=strLength2;
SET longStr = inParam1;
SET shortStr = inParam2;
ELSE
SET longStr=inParam2;
SET shortStr=inParam1;
end if;
-- select minStrLength,longStr,shortStr;
WHILE startIndex < minStrLength
DO
SET endIndex = startIndex + 1;
WHILE endIndex<=minStrLength
DO
SELECT SUBSTR(shortStr,startIndex+1,endIndex-startIndex) into tempStr from DUAL;
SELECT INSTR(longStr, tempStr ) into cmpInt FROM dual;
-- select startIndex,endIndex,tempStr,tempResult,cmpInt;
IF (cmpInt>0) AND (CHAR_LENGTH(tempResult)<char_length(tempstr)) then THEN
SET tempResult=tempStr;
END if;
SET endIndex = endIndex + 1;
end WHILE; -- endIndex while
set startIndex = startIndex + 1;
end while; -- startIndex WHILE
set resultStr=tempResult;
SELECT CHAR_LENGTH(resultStr) INTO resultLen FROM DUAL;
-- set resultLen= CHAR_LENGTH(resultStr);
-- select @resultLen,@resultStr;
END$$
DELIMITER ;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7282477/viewspace-2125118/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7282477/viewspace-2125118/