SQL与ORACLE分割字符串函数

ORACLE分割字符串函数:

create or replace function f_split_string(var_str in string,var_split In String) return t_ret_table 
is
var_out t_ret_table;
var_tmp varchar2(4000);
var_element varchar2(4000);
begin
var_tmp := var_str;
var_out := t_ret_table();
--如果存在匹配的分割符
while instr(var_tmp,var_split)>0 loop
var_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);
var_tmp := substr(var_tmp,instr(var_tmp,var_split)+length(var_split),length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_split_string;
/*
create or replace type t_ret_table is table of varchar2(20);
字符串分割的函数，返回数组，但是也可以使用语句查询
select * from table(f_split_string('hanjs-zhaos','-'));
*/

--获取"YYYY-MM"格式的当前系统时间

 trunc(SYSDATE,'dd')

SQL分割字符串函数

 

create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100))
returns @temp table(F1 varchar(100))
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)
insert @temp values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
end
return
end