我写过一个DB2函数,仅供参数,改改可能对你有用 。
CREATE FUNCTION strdec(v_src VARCHAR(10000))
RETURNS VARCHAR(32672)
SPECIFIC strdec
BEGIN ATOMIC
/**
* 功能说明: 列举输入的字符串中各字符的相关详细信息
* 作者: 540
* 日期:2012/12/07
* 实例:
* 1) 查看指定字符串信息 VALUES strdec('adfds.b?:#^……%¥~,。,。国88788');
* 2) 查看指定字符串信息 SELECT strdec('adfds.b?:#^……%¥~,。,。国88788') FROM sysibm.sysdummy1;
* 3) 查看表中字段的数据 SELECT strdec(err_stack) FROM TS_TASK_LOG WHERE err_stack IS NOT NULL FETCH FIRST 100 ROWS ONLY;
*/
DECLARE v_result VARCHAR(32672) DEFAULT '';
DECLARE v_left VARCHAR(32672) DEFAULT '';
DECLARE v_right VARCHAR(32672) DEFAULT '';
DECLARE v_char VARCHAR(3) DEFAULT '';
DECLARE v_hexchar VARCHAR(6) DEFAULT '';
DECLARE v_zm INTEGER DEFAULT 0 ;
DECLARE v_sz INTEGER DEFAULT 0 ;
DECLARE v_bd INTEGER DEFAULT 0 ;
DECLARE v_bd_desc VARCHAR(3000) DEFAULT '';
DECLARE v_unsee INTEGER DEFAULT 0;
DECLARE v_unsee_desc VARCHAR(3000) DEFAULT '';
DECLARE v_zwzf INTEGER DEFAULT 0;
DECLARE v_zwzf_desc VARCHAR(3000) DEFAULT '';
DECLARE v_zwzfqj INTEGER DEFAULT 0;
DECLARE v_zwzfqj_desc VARCHAR(3000) DEFAULT '';
DECLARE v_other INTEGER DEFAULT 0;
DECLARE v_charnum INTEGER DEFAULT 0;
DECLARE v_bytenum INTEGER DEFAULT 0;
SET v_charnum = character_length(v_src,CODEUNITS16);
SET v_bytenum = length(v_src);
SET v_right = v_src ;
WHILE length(v_right)>0
DO
SET v_left = v_right ;
SET v_right = substring(v_left,2,CODEUNITS16);
SET v_char = substring(v_left,1,1,CODEUNITS16);
IF length(v_char)=1 THEN /*单个字符占用1个字节*/
IF (ascii(v_char)>=65 AND ascii(v_char)<=90) OR /*大小写字母*/
(ascii(v_char)>=97 AND ascii(v_char)<=122) THEN
SET v_zm = v_zm + 1 ;
ELSEIF ascii(v_char)>=48 AND ascii(v_char)<=57 THEN /*数字0-9*/
SET v_sz = v_sz + 1 ;
ELSEIF (ascii(v_char)>=32 AND ascii(v_char)<=47) OR /*其它英文标点符号*/
(ascii(v_char)>=58 AND ascii(v_char)<=64) OR
(ascii(v_char)>=91 AND ascii(v_char)<=96) OR
(ascii(v_char)>=123 AND ascii(v_char)<=126) THEN
SET v_bd = v_bd +1 ;
IF locate(v_char,v_bd_desc)=0 THEN
SET v_bd_desc = v_bd_desc || ' ' || v_char ;
END IF ;
ELSE /*其它不可见字符*/
SET v_unsee = v_unsee + 1 ;
IF locate(trim(char(ascii(v_char))),v_unsee_desc)=0 THEN
SET v_unsee_desc = v_unsee_desc || ' chr(' || trim(char(ascii(v_char)))||')' ;
END IF ;
END IF;
ELSEIF length(v_char)=3 THEN /*单个字符占用3个字节,这里指中文*/
SET v_hexchar = char(hex(substr(v_char,1,1)))||char(hex(substr(v_char,2,1)))||char(hex(substr(v_char,3,1))) ;
IF v_hexchar='EFBC8C' OR /*中文符号*/
v_hexchar='E28094' OR
v_hexchar='E38081' OR
v_hexchar='E38082' OR
v_hexchar='EFBC9F' OR
v_hexchar='EFBC9B' OR
v_hexchar='EFBC9A' OR
v_hexchar='E28099' OR
v_hexchar='E2809D' OR
v_hexchar='E38090' OR
v_hexchar='E38091' OR
v_hexchar='EFBC81' OR
v_hexchar='EFBFA5' OR
v_hexchar='E280A6' OR
v_hexchar='EFBC88' OR
v_hexchar='EFBC89' THEN
SET v_zwzf = v_zwzf + 1 ;
IF locate(v_char,v_zwzf_desc)=0 THEN
SET v_zwzf_desc = v_zwzf_desc || ' ' ||v_char ;
END IF ;
ELSEIF v_hexchar='E38080' OR /*全角字符*/
v_hexchar='EFBCA0' OR
v_hexchar='EFBC83' OR
v_hexchar='EFBC85' OR
v_hexchar='EFBC86' OR
v_hexchar='C39720' OR
v_hexchar='EFBD9B' OR
v_hexchar='EFBD9D' OR
v_hexchar='EFBD9E' OR
v_hexchar='EFBCBC' THEN
SET v_zwzfqj = v_zwzfqj + 1 ;
IF locate(v_char,v_zwzfqj_desc)=0 THEN
SET v_zwzfqj_desc = v_zwzfqj_desc || ' ' ||v_char ;
END IF ;
ELSE
SET v_other = v_other + 1 ;
END IF;
ELSE
END IF ;
END WHILE;
SET v_result = '检测到字符个数:'||trim(char(v_charnum))||',占用字节大小:'||trim(char(v_bytenum))||chr(13)||chr(10)||
'原字符串是:'||v_src||chr(13)||chr(10)||
'可见字符:字母:'||trim(char(v_zm))||',数字:'||trim(char(v_sz))||',标点符号:'||trim(char(v_bd))||(CASE WHEN v_bd>0 THEN ',分别是:'||v_bd_desc ELSE '' END)||chr(13)||chr(10)||
'不可见字符:'||trim(char(v_unsee))||(CASE WHEN v_unsee>0 THEN ',分别是:'||v_unsee_desc ELSE '' END)||chr(13)||chr(10)||
'中文字符:'||trim(char(v_zwzf))||(CASE WHEN v_zwzf>0 THEN ',分别是:'||v_zwzf_desc ELSE '' END)||chr(13)||chr(10)||
'中文全角字符:'||trim(char(v_zwzfqj))||(CASE WHEN v_zwzfqj>0 THEN ',分别是:'||v_zwzfqj_desc ELSE '' END)||chr(13)||chr(10)||
'其它字符(包含汉字):'||trim(char(v_other));
RETURN v_result;
END;
收起