有这样一个字符串:“您好:您已经成功新建一条${INVOICE_TYPE},单号为:${REQ_INVOICE_ID},详细请登录系统查询。”
其中 ${INVOICE_TYPE}和${REQ_INVOICE_ID}为宏变量。也就是说字符串中有0到n个宏变量,不同的宏变量长度固定,比如INVOICE_TYPE长度为2,REQ_INVOICE_ID长度为5,根据宏变量的名称就可以知道宏变量的长度,那么如何计算这个字符串的长度?
其中 ${INVOICE_TYPE}和${REQ_INVOICE_ID}为宏变量。也就是说字符串中有0到n个宏变量,不同的宏变量长度固定,比如INVOICE_TYPE长度为2,REQ_INVOICE_ID长度为5,根据宏变量的名称就可以知道宏变量的长度,那么如何计算这个字符串的长度?
在oracle中计算字符串的函数为 length(目标字段)
代码如下:
create or replace function fn_template_content_length(v_content varchar2)
return number
is
/*把宏变量以及宏变量前面的子串合起来看做一个匹配模型,一次循环处理一个模型,直到处理完最后一个*/
vi_strlen number(10):=0;
vi_str_next_length number(10);
vv_str varchar2(100);
vv_str_next varchar2(100);
vv_str_current varchar2(100);
begin
vv_str_current:=v_content;
--根据 宏变量 把字符串截取成多个子串,分别计算子串长度,再相加得出总长度
while instr(vv_str_current,'${')<>0 loop
--宏变量前面的子串
vv_str:= substr(vv_str_current,0,instr(vv_str_current,'$')-1);
vv_str_next:=substr(vv_str_current,instr(vv_str_current,'$'),instr(vv_str_current,'}')-instr(vv_str_current,'$')+1);
vi_strlen:=nvl(vi_strlen,0)+nvl(length(vv_str),0);
--宏变量子串
vv_str_next:= rtrim(ltrim(vv_str_next,'${'),'}');
select nvl(macro_length,0) into vi_str_next_length from macro_var_length where trim(upper(macro_name))=upper(vv_str_next);
vi_strlen:=nvl(vi_strlen,0)+nvl(vi_str_next_length,0) ;
--剩余字符串,继续循环处理
vv_str_current:=substr(vv_str_current,instr(vv_str_current,'}')+1);
end loop;
--所有宏变量处理完后,剩下最后一个不包含宏变量的子串,直接计算长度即可
vi_strlen:=nvl(vi_strlen,0)+nvl(length(vv_str_current),0);
return(vi_strlen);
end fn_template_content_length;
2 (macro_name varchar2(100),
3 macro_length number);
Table created
SQL> insert into macro_var_length
2 select '${INVOICE_TYPE}',4 from dual;
1 row inserted
SQL> insert into macro_var_length
2 select '${REQ_INVOICE_ID}',2 from dual;
1 row inserted
SQL> with t as (select regexp_substr('f${INVOICE_TYPE}f${REQ_INVOICE_ID}fdasfdsa',
2 '(\$\{)[a-zA-Z_]+\}',
3 1,
4 level) str
5 from dual
6 connect by level <= length('f${INVOICE_TYPE}f${REQ_INVOICE_ID}fdasfdsa') -
7 length(replace('f${INVOICE_TYPE}f${REQ_INVOICE_ID}fdasfdsa',
8 '$',
9 '')))
10 select length('f${INVOICE_TYPE}f${REQ_INVOICE_ID}fdasfdsa') +
11 sum(macro_length - length(t.str))
12 from macro_var_length a, t
13 where a.macro_name = t.str;
LENGTH('F${INVOICE_TYPE}F${REQ
------------------------------
16
SQL>
declare
v_content varchar2(2000);
sText varchar2(2000);
iLen number;
begin
iLen := 0;
v_content := '${INVOICE_TYPE}和${REQ_INVOICE_ID}';
select length(v_content) into iLen from dual;
dbms_output.put_line(iLen); select replace(v_content,'$','') into sText from dual;
select replace(sText,'{','') into sText from dual;
select replace(sText,'}','') into sText from dual;
select length(sText) into iLen from dual;
dbms_output.put_line(ilen);
end;这种方法应比较好。只要把 '$', '{', '}' 替换成''