存储过程拆分字符串

A. 如何在存储过程中分割逗号分隔的字符串

create function f_split(@Sourcesql varchar(8000)) returns @temp table(a varchar(100)) as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(',',@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(',',@SourceSql) end if @SourceSql<>',' insert @temp values(@SourceSql) return end 用法 select * from dbo.f_split('abcd,efg,mm')

B. 求助：如何在存储过程中拆分字符串

declare @inputStr varchar(max)set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'declare @divideFlag varchar(20)set @divideFlag='H,'declare @bnname varchar(8)declare @gpdm varchar(8)declare @gpcn intdeclare @ydate intdeclare @yopen intdeclare @yhigh intdeclare @substr varchar(200)declare @pos1 intdeclare @pos2 intdeclare @sql varchar(400)--如果字符串中有分隔符标记while CHARINDEX(@divideFlag,@inputStr,0)0begin set @pos1 = CHARINDEX(@divideFlag,@inputStr,0) print '位置1 '+cast(@pos1 as varchar) set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1) print '位置2 ' + cast(@pos2 as varchar) if @pos20 begin set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1) print '截取处理串 '+ @substr set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr)) --print @inputStr end else begin set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr)) print '截取处理串 '+ @substr set @inputStr = 'empty' --print @inputStr end --去掉开头的分割标记 set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr)) print '去掉开头分割标记 ' + @substr if right(@substr,1)=',' begin set @substr = left(@substr,len(@substr)-1) print '去掉末尾的逗号' + @substr end --取得表名称 set @bnname = substring(@substr,1,charindex(',',@substr,0)-1) print @bnname --取表名称后面的字符串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr --取得第一个字段 set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1) print @gpdm --取得后面的字符串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1) print @gpcn set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @ydate = substring(@substr,1,charindex(',',@substr,0)-1) print @ydate set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yopen = substring(@substr,1,charindex(',',@substr,0)-1) print @yopen set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1) print @yhigh set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr -- 拼接 插入的 sql 字符串 set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh) values( '''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+' )' print @sqlend-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用

C. 关于oracle 存储过程 如何切割一个字符串 转化为字符数组，然后遍历该数组： 类似ab，12；cd，55；k，7

给你写了个包，包体代码如下：
CREATE OR REPLACE PACKAGE BODY Pkg_Bai IS
-- 自定义个数组类型
TYPE Typ_Str_Array IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
PROCEDURE Prc_String_To_Array(Pv_i_String IN VARCHAR2,
Pv_i_Identifier IN VARCHAR2,
Pt_o_Array OUT Typ_Str_Array) IS

Lv_Tmpstr VARCHAR2(200);
Lv_Changed_String VARCHAR2(4000);

BEGIN

-- 初始化
Lv_Changed_String := Pv_i_String;

-- 拆分逻辑
-- 1.字符串中没有标识符
IF Instr(Pv_i_String, Pv_i_Identifier, 1, 1) = 0 THEN
Pt_o_Array(Pt_o_Array.Count + 1) := Pv_i_String;
ELSE

-- 2.字符串中有标识符
WHILE Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) > 0 LOOP
Lv_Tmpstr := Substr(Lv_Changed_String,
1,
Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) - 1);

Pt_o_Array(Pt_o_Array.Count + 1) := Lv_Tmpstr;
Lv_Changed_String := Substr(Lv_Changed_String,
Instr(Lv_Changed_String, Pv_i_Identifier, 1, 1) + 1,
Length(Lv_Changed_String));
END LOOP;

-- 3.最有一个被拆分出来的字符串中不含标识符
IF NOT Lv_Changed_String IS NULL THEN
-- 将最后一个保存
Pt_o_Array(Pt_o_Array.Count + 1) := Lv_Changed_String;
END IF;

END IF;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

PROCEDURE Prc_Test(Pv_i_Str IN VARCHAR2) IS
--定义数组
Lt_Array Typ_Str_Array;
Ln_Count NUMBER; -- 数组元素个数
Ln_Num NUMBER;
Lv_Value VARCHAR2(30);
BEGIN

Prc_String_To_Array(Pv_i_Str, ';', Lt_Array);
-- 循环数组
FOR i IN 1 .. Lt_Array.Count LOOP
-- 获取每个元素中数值
Ln_Num := To_Number(TRIM(Substr(Lt_Array(i), Instr(Lt_Array(i), ',', -1) + 1)));
IF Ln_Num > 20 THEN
Lv_Value := TRIM(Substr(Lt_Array(i), 1, Instr(Lt_Array(i), ',', -1) - 1));
INSERT INTO a (col_a) VALUES (lv_value);
END IF;
END LOOP;
END;

END Pkg_Bai;

调用Prc_Test来分隔字符串并按要求比较后插入a表。我测试过，没有问题。
需要注意的是，数据的要求：字符串中每组数据间使用英文小写分号分隔，同一组数据字母在前，数字在后，以英文小写逗号分隔。测试一下吧。
欢迎采纳，不采纳你都对不起我这20分钟。呵呵......

D. db2如何使用存储过程拆分字符串

declare @inputStr varchar(max)set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'declare @divideFlag varchar(20)set @divideFlag='H,'declare @bnname varchar(8)declare @gpdm varchar(8)declare @gpcn intdeclare @ydate intdeclare @yopen intdeclare @yhigh intdeclare @substr varchar(200)declare @pos1 intdeclare @pos2 intdeclare @sql varchar(400)--如果字符串中有分隔符标记while CHARINDEX(@divideFlag,@inputStr,0)0begin set @pos1 = CHARINDEX(@divideFlag,@inputStr,0) print '位置1 '+cast(@pos1 as varchar) set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1) print '位置2 ' + cast(@pos2 as varchar) if @pos20 begin set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1) print '截取处理串 '+ @substr set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr)) --print @inputStr end else begin set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr)) print '截取处理串 '+ @substr set @inputStr = 'empty' --print @inputStr end --去掉开头的分割标记 set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr)) print '去掉开头分割标记 ' + @substr if right(@substr,1)=',' begin set @substr = left(@substr,len(@substr)-1) print '去掉末尾的逗号' + @substr end --取得表名称 set @bnname = substring(@substr,1,charindex(',',@substr,0)-1) print @bnname --取表名称后面的字符串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr --取得第一个字段 set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1) print @gpdm --取得后面的字符串 set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1) print @gpcn set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @ydate = substring(@substr,1,charindex(',',@substr,0)-1) print @ydate set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yopen = substring(@substr,1,charindex(',',@substr,0)-1) print @yopen set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1) print @yhigh set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr)) print @substr -- 拼接 插入的 sql 字符串 set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh) values( '''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+' )' print @sqlend-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用

E. 如何拆分调用SQL存储过程后返回的字符串

string ID = Table[0].Rows[0][0].ToString();

sting[] arrID=ID.split(',');

把对象转换成字符串不就可以了吗？呵呵
希望对你有帮助

F. 我就是想在存储过程当中，传入一个字符串（不知道长度的）。然后把这个字符传里面的字符全部拆分开来。

给你个存储过程

CREATE PROCEDURE p_split
@c varchar(8000),
@split varchar(5)

as
declare @t table( word varchar(20) )

begin

while(charindex(@split,@c)<>0)
begin
insert @t( word) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'') -- 删除指定长度的字符并在指定的起始点插入另一组字符
end
insert @t(word) values (@c) ---最后一个不含分隔的加进来
select distinct * from @t
end
GO

还是用表变量

G. Oracle中怎样用存储过程将一段字符串以逗号为界截取,并将得到的子串存到另一张表中

H. SQL 存储过程 长字符串分割存储

declare @inputStr varchar(max)
set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'

declare @divideFlag varchar(20)
set @divideFlag='H,'

declare @bnname varchar(8)
declare @gpdm varchar(8)
declare @gpcn int
declare @ydate int
declare @yopen int
declare @yhigh int

declare @substr varchar(200)
declare @pos1 int
declare @pos2 int

declare @sql varchar(400)

--如果字符串中有分隔符标记
while CHARINDEX(@divideFlag,@inputStr,0)>0
begin
set @pos1 = CHARINDEX(@divideFlag,@inputStr,0)
print '位置1 '+cast(@pos1 as varchar)
set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1)
print '位置2 ' + cast(@pos2 as varchar)

if @pos2>0
begin
set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1)
print '截取处理串 '+ @substr
set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr))
--print @inputStr
end
else
begin
set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr))
print '截取处理串 '+ @substr
set @inputStr = 'empty'
--print @inputStr
end
--去掉开头的分割标记
set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr))
print '去掉开头分割标记 ' + @substr

if right(@substr,1)=','
begin
set @substr = left(@substr,len(@substr)-1)
print '去掉末尾的逗号' + @substr
end
--取得表名称
set @bnname = substring(@substr,1,charindex(',',@substr,0)-1)
print @bnname
--取表名称后面的字符串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
--取得第一个字段
set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpdm
--取得后面的字符串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpcn
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @ydate = substring(@substr,1,charindex(',',@substr,0)-1)
print @ydate
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yopen = substring(@substr,1,charindex(',',@substr,0)-1)
print @yopen
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1)
print @yhigh
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
-- 拼接 插入的 sql 字符串
set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh)
values(
'''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+'
)'

print @sql
end
-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用

I. mysql存储过程中 如何拆分字符串

MySQL分割字符串应该如何实现呢？下面就为您介绍MySQL分割字符串的实现方法步骤，希望可以让您对MySQL分割字符串有更多的了解。

例如：update `edw_video` set company = substring(keyword,1,length(SUBSTRING_INDEX(keyword,",",1)))

名称：SPLIT_SUB_STR1（取得split字符串的第一段）
参数：str 要split的目标字符串 （输入格式为‘11，2，3，4，5，6’； 输出为‘2，3，4，5，6’）
delimiter 分隔符 （‘，’）
split1 输出第一项（‘11’）
存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `SPLIT_SUB_STR0`(inout str VARCHAR(1000) ,delimiter VARCHAR(1), out split1 VARCHAR(20))

BEGIN
# 名称 SPLIT_SUB_STR1 取得分割字符串的第一段字符
# 参数inout str VARCHAR(1000) ,delimiter VARCHAR(1), out split0 VARCHAR(20)
# 输入完整字符串，输出去掉取得第一项后的字符串； 分隔符； 输出split的第一个项目

#分割出第一段字符串不包括分隔符的长度
DECLARE SUB_STR_LENGTH INT;

# SUBSTRING_INDEX函数取得目标字符串左侧第n个分割符左侧的部分，n为负时返回右侧第n个的右部分
SET SUB_STR_LENGTH = length(SUBSTRING_INDEX(str,delimiter,1));

#截取第一段字符串，不包括分隔符，放入输出参数里
SET split0 = substring(str, 1, SUB_STR_LENGTH);

# 取得去掉第一个字符串和分隔符的字符串，进行下次循环取得下个字符串
SET str = substring(str, SUB_STR_LENGTH + 2 );
#测试一下输出是否正确
#SELECT SUB_STR_LENGTH, SUB_STR_SPLIT0, STR;
END

调用实例

CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
#目标字符串
set @a = ’1,2,3,4,5,6,12‘;
# 分隔符
set @c = ',';
# 存储风格后的字符串
set @b = '';

REPEAT
# 调用上面的存储过程
CALL SPLIT_SUB_STR0(@a, ',', @c);
#将取得的字符串拼接，测试用
set @b = concat(@b, @c);
#当目标字符串为空时，停止循环
UNTIL @a = ''
END REPEAT;
# 查看结果
select @a, @c, @b;

END;

J. Oracle中怎么实现类似数组的功能，比如存储过程我传入一个长字符串，按照固定格式拆分成多个

给你个存储过程

CREATE PROCEDURE p_split
@c varchar(8000),
@split varchar(5)

as
declare @t table( word varchar(20) )

begin

while(charindex(@split,@c)<>0)
begin
insert @t( word) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'') -- 删除指定长度的字符并在指定的起始点插入另一组字符
end
insert @t(word) values (@c) ---最后一个不含分隔的加进来
select distinct * from @t
end
GO

还是用表变量