临时写的记笔记
最近需要用到格式字符,就写了一下,估计存在小bug,不过酬和着用了。
str1 的大小可以根据需要修改
-------------------------------------
--格式化字符串函数 只能一位加间隔符号
--select strutil('12345',',') from dual
--1,2,3,4,5
-------------------------------------
create or replace function strutil(strformat varchar2,args varchar)return varchar2
as
i integer:=0;
j integer:=0;
str1 varchar2(100) :='';
begin
if strformat is null then
return '';
end if;
select length(strformat) into j from dual;
if j = 1 then
return strformat;
end if;
for i in 1..j loop
if i = j then
str1 := str1||substr(strformat,i,1);
else
str1 := str1||substr(strformat,i,1)||args;
end if;
end loop;
return str1;
end strutil;
--------------------------------
select strutil('12',',') from dual;
select null from dual;
-------------------------------------
--格式化字符串函数 扩展 strformat必须是字符串 args需要添加的符号 num 需要相几位分割 必须保持strformat长度正好可以被num整分
--例如:strformat='123456' args='-' num=1/2/3/6或者大于6的数
--num=1 '1-2-3-4-5-6'
--num=2 '12-34-56'
--num=3 '123-456'
--num>=6 '123456'
-------------------------------------
create or replace function strutil_two(strformat varchar2,args varchar,num integer)return varchar2
as
i integer:=1;
j integer:=0;
k number :=0;
str1 varchar2(100) :='';
begin
if strformat is null then
return '';
end if;
select length(strformat) into k from dual;
if k <= num then
return strformat;
end if;
select length(strformat)/num into j from dual;
while i <= j*num loop
if i = 1 then
str1 := str1||substr(strformat,1,num)||args;
elsif i = (j-1)*num+1 then
str1 := str1||substr(strformat,i,num);
else
str1 := str1||substr(strformat,i,num)||args;
end if;
i := i+num;
exit when i > j*num;
end loop;
return str1;
end strutil_two;
--------------------------------
select strutil_two('123456789123',',',6) from dual;