如何把一串字符拆分成多个字段插入数据库中在sql中实现
在sql存储过程中实现
例如:s='12,abcd,3467,sdf,bvmvnbm,std'
S是动态的有可能还不只有这些
把它拆分成两条或者多条记录插入表中如下所示:
字段 A B C
12 abcd 3467
sdf bvmvnbm std
在sql存储过程中实现
例如:s='12,abcd,3467,sdf,bvmvnbm,std'
S是动态的有可能还不只有这些
把它拆分成两条或者多条记录插入表中如下所示:
字段 A B C
12 abcd 3467
sdf bvmvnbm std
@exmple_id int,@RATING_ID VARCHAR(20),@Item_id VARCHAR(30), @Cust_Sign bit,
@DIS_COST numeric(9,2),@GET_COST numeric(9,2),@COST_SIGN decimal(10,2),
@DISCOST_PER numeric(9,2),@DISCOST_PRICE decimal(10,2), @OPERTIME datetime,
@operid int,@opername varchar(40),@stationno int,@exmple_name varchar(50),
@substring varchar(700),
@ErrMsg varchar(100) out
as
declare @itemcode varchar(10)
declare @item_name varchar(20)
declare @recod_type varchar(10)
DECLARE @Index int
select @exmple_id =max(exmple_id) from exmple
if @exmple_id is null select @exmple_id=@exmple_id+1
select @exmple_id= @exmple_id + 1
begin tran formula_add_small
delete [dbo].[Technicians_attached] where exmaple_id=@exmple_id
if @@Error<>0
begin
Rollback Tran formula_add_small
Set @ErrMsg='添加中工方案失败'
Return -1
end
while CHARINDEX(',',@substring) > 0
begin
SELECT @Index = CHARINDEX(',',@substring)
SELECT @itemcode = LEFT(@substring,@Index-1)
select @item_name=LEFT(@substring,@Index-2)
select @recod_type=LEFT(@substring,@Index-3)
INSERT INTO [dbo].[Technicians_attached]([exmaple_id], [itemcode], [item_name],recod_type)
VALUES(@exmple_id, @itemcode, @item_name,@recod_type)
SELECT @substring = REPLACE(@substring,@itemcode+',',''); end
if @@Error<>0
begin
Rollback Tran formula_add_small
Set @ErrMsg='添加中工方案失败'
Return -1
end
INSERT INTO [dbo].[EXMple]([Exmple_id], [RATING_ID], [Cust_Sign], [DIS_COST], [GET_COST], [COST_SIGN], [DISCOST_PER], [DISCOST_PRICE], [OPERTIME], [OPERID], [OperName], [StationNO])
VALUES(@Exmple_id, @RATING_ID, @Cust_Sign, @DIS_COST, @GET_COST, @COST_SIGN, @DISCOST_PER, @DISCOST_PRICE, @OPERTIME, @OPERID, @OperName, @StationNO)
if @@Error<>0
begin
Rollback Tran formula_add_small
Set @ErrMsg='添加中工方案失败'
Return -1
end
INSERT INTO [dbo].[exmaple_Name]([Exmple_id], [EXMPLE_NAME])
VALUES(@Exmple_id, @EXMPLE_NAME )
if @@Error<>0
begin
Rollback Tran formula_add_small
Set @ErrMsg='添加中工方案失败'
Return -1
end
commit tran formula_add_smallGO(也就是这个存储过程如何来修改)
用delphi来做就很简单了。const MaxFieldCount = 3;
var sl :TStringList;sl := TStringList.Create;
sl.CommaText := s;
for i:=0 to sl.Count DIV 3 -1 do
begin
sql := 'insert t (a,b,c) values(' + QuotedStr(sl[i*3]) + ','
+ QuotedStr(sl[i*3]+1) + ',' +
+ QuotedStr(sl[i*3]+2) + ')';
end;
var
vList: TStringList;
i: integer;
a, b, c: string;
k: integer;
const
s='12,abcd,3467,sdf,bvmvnbm,std';
begin
k := 0;
vList := TStringList.Create;
try
vList.DelimitedText := a;
for i := 0 to vList.count - 1 do
begin
if i mod 3 = 0 then a := vList.strings[i]
if i mod 3 = 1 then b := vList.strings[i];
if i mod 3 = 2 then c := vList.strings[i];
k := k + 1;
if k = 3 then
begin
//插入SQL语句
//恢复k值
k := 0;
end;
end;
finally
vList.Free;
end;
end;
要求在存储过程中来实现的
begin SELECT @Index = CHARINDEX(',',@substring)
SELECT @itemcode = LEFT(@substring,@Index-1)
select @item_name=LEFT(@substring,@Index-2)
select @recod_type=LEFT(@substring,@Index-3)
INSERT INTO [dbo].[Technicians_attached]([exmaple_id], [itemcode], [item_name],recod_type)
VALUES(@exmple_id, @itemcode, @item_name,@recod_type)
SELECT @substring = REPLACE(@substring,@itemcode+',',''); end
主要是要修改这段
declare @str varchar(1000)set @str = '12,abcd,3467,sdf,bvmvnbm,std'declare @tb table(a varchar(200))insert into @tb values(@str)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b --下面改为用游标读就行了
SELECT SUBSTRING(@str, B.id, CHARINDEX(',', @str + ',', B.id) - B.id)
FROM @tb A, # B
WHERE SUBSTRING(',' + @str, B.id, 1) = ','/* result
12
abcd
3467
sdf
bvmvnbm
std
*/DROP TABLE #
@recod_type
@itemcode
怎么赋值
declare @WrMark int declare @itemcode varchar(20),@substring varchar(700)
declare @item_name varchar(20)
declare @recod_type varchar(10),@exmple_id int
select @substring = '111,aaa,1,222,bbb,2,33333,cccccc,5,44444,dddddd,6'
select @pos=1
select @ix=1
select @WrMark = 1
select @exmple_id=1
while @ix>0
begin
set @ix=charindex(',',@substring,@pos)
if @ix>0
set @str=substring(@substring,@pos,@ix-@pos)
else
set @str=substring(@substring,@pos,len(@substring))
set @str=ltrim(rtrim(@str))
if @str <> ''
begin
select @WrMark = @WrMark + 1 if @WrMark = 2
begin
select @itemcode=@str
end
if @WrMark = 3
begin
select @item_name=@str
end
if @WrMark = 4
begin
select @recod_type=@str
end
if @WrMark >= 4
begin
--print @itemcode+' '+@item_name+' '+@recod_type
-- select @itemcode = ''
-- select @item_name = ''
-- select @recod_type = ''
INSERT INTO [Technicians_attached]([exmaple_id], [itemcode], [item_name], [recod_type])
VALUES(@exmple_id, @itemcode, @item_name,@recod_type)
select @WrMark = 1
end end
set @pos=@ix+1
end