很多朋友来Email反映,AO不能处理带分隔符的科目编码,尤其是相同级次的科目编码长度不同时。为解决广大审计同仁困惑,笔者自行开发一个函数fn_sub_string:实现从带分隔符的字符串中,提取指定顺序的子串。有了这个基本功能的函数,审计人员可以借用这个函数再配合内置的函数,就能彻底解决这类问题。 一、开发背景 不少财务软件的科目表,以分隔符号分隔不同的级次的科目,且相同级次的科目长度不等,如:5001-01-010-17,301-002-03等,很明显,这种不规则的科目编码是无法导入AO的,需要将相同级次的科目编码,设置为相同的长度(每级应设置为该级的最大长度为宜)。如果使用SQL语句处理,在科目级次较多时,代码冗长,十分烦琐。因此,需要有一个函数,能自由提取指定级次的子串,提取出子串后,处理就方便多了。 二、功能实现 对于给定的带分隔符的字符串,从中提取出指定级次的子串,函数的功能应满足基本的需求,实现特定的功能,这样才具有通用性。 三、函数设计 1.判断字符串的末尾是否存在指定分隔符,如果没有,添加指定分隔符; 2.从首字符位置s(s=1)开始,查找出现的分隔符的位置e; 3.从s开始,提取e-i个字符,存入表变量或索引表中,并指定索引号n的初始值1; 4.将步骤(2)查找到的分隔符位置加分隔符长度(s=e+len(分隔符)),作为再次查找位置,继续查找从该位置起下一个分隔符的位置,然后使用步骤(3)计算子串长度并提取子串,再次存入表变更或索引表中,其索引号为2(n+1); 5.重复(3)-(4),直至结束; 6.当n的值与需要提取的子串顺序相同时,终止查找。 1.ORACLE版本:CREATEORREPLACEFUNCTIONfn_sub_string(CODE INVARCHAR2,SPLIT INvarchar2,lvl ININTEGER) RETURNVARCHAR2 TYPEtyp_sub_stringISTABLEOFVARCHAR2(32767)INDEXBYBINARY_INTEGER; sub_stringtyp_sub_string; iINTEGER:=1; s INTEGER:=1; e INTEGER; n INTEGER; new_codeVARCHAR2(32767); BEGIN IF SUBSTR(CODE,LENGTH(CODE)-LENGTH(SPLIT)+1,LENGTH(SPLIT)<>SPLIT THEN new_code:=CODE||SPLIT; new_code:=CODE; ENDIF; n:=(LENGTH(new_code)-LENGTH(replace(new_code,SPLIT,"))/LENGTH(SPLIT); WHILEi<=n LOOP e:=INSTR(new_code,SPLIT,s); sub_string(i):=substr(new_code,s,e-s); s:=e+LENGTH(SPLIT); IFlvl=i THENEXIT; —优化处理 ENDIF; i:i+1; ENDLOOP; RETURNsub_string(lvl); 2.SQL SERVER版本: CREATEfunction[dbo].[fn_sub_string](@code varchar(max),@split varchar(max),@lvlint) returnsvarchar(max) begin declare @cnint,@s int=1,@e int,@length int,@n int=1; declare @code_subv arch ar(m ax),@code_newvarchar(max),@result varchar(max); declare @tbtable (n intprimaryk ey,codevarchar(max)); set @code_sub=right(@code,len(@split)); if @code_sub<>@split set @code_new=@code+@split; set @code_new=@code; set @cn=(len(@code_new)-len(replace(@code_new,@split,"))/len(@split); —将字符依次取出存入@tb变量中 while @n<=@cn begin set @e =charindex(@split,@code_new,@s); set @length=@e-@s; set @code_sub=substring(@code_new,@s,@length); insertinto @tbvalues(@n,@code_sub); set @s=@e+len(@split);