求自定义TRIM函数
各位大侠,请问怎么写一个DB2的自定义的TRIM函数?
这个函数要能去掉输入字符的前导及拖尾字符,前导及拖尾字符包括从NULL (\u0000) 到SPACE (\u0020) 间的所有字符.
这些字符可能为:
TAB
SPACE
打印控制符
换行符
总之,只要范围在NULL (\u0000) 到SPACE (\u0020)之间的,都需要TRIM掉.
急,请兄弟们支持. db2自己的带的不能满足？那只能自己用c写了。 我自己搞了一个纯SQL版的,不过我更想要C版.
CREATEFUNCTION TrimChar (
v_instr VARCHAR(4000)
)
RETURNS VARCHAR(4000)
LANGUAGE SQL
------------------------------------------------------------------------
-- FUNCTION: SUPERTRIM
-- Description:
--
------------------------------------------------------------------------
BEGIN ATOMIC
DECLARE v_len INTEGER; -- Length of string
DECLARE v_lcodeINTEGER; -- Ascii code of the left char
DECLARE v_rcodeINTEGER; -- Ascii code of the right char
DECLARE v_lflagINTEGER; -- End flag for left hand
DECLARE v_rflagINTEGER; -- End flag for right hand
DECLARE v_outstr VARCHAR(4000);-- Output string
IF v_instr is NULL THEN
RETURN NULL;
END IF;
SET v_outstr = v_instr;
SET v_len = Length(v_outstr);
SET v_lflag= 0;
SET v_rflag= 0;
WHILE v_lflag*v_rflag = 0 DO
SET v_lcode = Ascii(Left(v_outstr,1));
SET v_rcode = Ascii(Right(v_outstr,1));
IF (v_lcode between 0 and 32) THEN
SET v_outstr = Right(v_outstr,v_len-1);
SET v_len = Length(v_outstr);
ELSE
SET v_lflag = 1;
END IF;
IF (v_rcode between 0 and 32) THEN
SET v_outstr = Left(v_outstr,v_len-1);
SET v_len = Length(v_outstr);
ELSE
SET v_rflag = 1;
END IF;
END WHILE;
RETURN v_outstr;
END
;
页:
[1]