分割字符串后在表格中插入值 [英] Insert values into table after splitting the string
问题描述
我想将值插入到employee表中。
这些值以字符串格式〜
分隔
例如: AA 〜B〜123
我用下面的函数分割它:
pre > CREATE FUNCTION [db_owner]。[FN_Split](@String varchar(8000),@Delimiter char(1))
返回@temptable TABLE(items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
选择@idx = 1
if len(@String)< 1或@String为空返回
,而@idx!= 0
begin
set @idx = charindex(@ Delimiter,@ String)
if @idx! = 0
set @slice = left(@ String,@ idx - 1)
else $ b $ set @slice = @String
if(len(@slice) > 0)
插入到@temptable(Items)值中(@slice)
set @String = right(@ String,len(@String) - @idx)
if len(@String)= 0 break
end
返回
结束
现在我得到输出为
SELECT * FROM db_owner.FN_Split('AA〜B〜123','〜')
$ b $输出 项目
______
AA
B
123
现在我卡在这里了
如何在雇员表中插入上述值???
像
insert into employee(name,add,phone)
values('AA','B','123');
请指导。
不工作
insert into employee
SELECT * FROM db_owner.FN_Split('AA〜BB〜CC','〜 ')
错误
消息213,级别16,状态1,行1
提供的值的列名称或数量与表定义不匹配。
如果您可以在存储过程中添加一个小计数器像这样,然后生活会更容易:
$ b $ pre $ CREATE FUNCTION [db_owner]。[FN_Split](@String varchar(8000),@分隔符char(1))
返回@temptable TABLE(orderId int,items varchar(8000))
as
begin
declare @idx int
declare @slice varchar (8000)
declare @orderId int = 0 - <添加一个计数器
如果len(@String)<1或@String是@idx = 1
返回
@idx!= 0
begin
set @idx = charindex(@ Delimiter,@ String)
if @idx!= 0
@ set @slice = left(@ String,@ idx - 1)
else
set @slice = @String
if(len(@slice) > 0)
插入@temptable(orderId,Items)值(@orderId,@slice)
set @orderId = @ orderId + 1 - <增加计数器
set @String = right(@ String,len(@String) - @idx)
if len(@String)= 0 break
end
return
end
您的后续查询可能类似于以下内容:
< pre $
DECLARE @name varchar(50)=(SELECT items FROM db_owner.FN_Split('AA〜BB〜CC','〜')where orderId = 0)
DECLARE @添加varchar(50)=(SELECT items FROM db_owner.FN_Split('AA〜BB〜CC','〜')where orderId = 1)
DECLARE @phone varchar(50)=(SELECT items FROM db_owner.FN_Split ('AA〜BB〜CC','〜')where orderId = 2)
插入员工
(
名称,
add,
phone
)
值
(
@name,
@add,
@phone
)
但是您是否尝试过更改过程,以便以水平格式输出数据,而不是当前的垂直输出?
I want to insert values into employee table.
And those values are in string format ~
separated
E.g: AA~B~123
I am splitting it using following function
CREATE FUNCTION [db_owner].[FN_Split] (@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Now I get Output as
SELECT * FROM db_owner.FN_Split('AA~B~123','~')
Output
items
______
AA
B
123
Now I am stuck here
How can I insert above values in employee table???
like
insert into employee (name,add,phone)
values('AA','B','123');
Please guide.
Tried this but not working
insert into employee
SELECT * FROM db_owner.FN_Split('AA~BB~CC','~')
ERROR
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
if you could add a small counter into the stored procedure like this then life would be easier:
CREATE FUNCTION [db_owner].[FN_Split] (@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (orderId int,items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
declare @orderId int = 0 --<added a counter
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(orderId, Items) values(@orderId, @slice)
set @orderId = @orderId+1 --<increment the counter
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Your subsequent query could be something like the following:
DECLARE @name varchar(50) = (SELECT items FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 0)
DECLARE @add varchar(50) = (SELECT items FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 1)
DECLARE @phone varchar(50) = (SELECT items FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 2)
insert into employee
(
name,
add,
phone
)
values
(
@name,
@add,
@phone
)
But have you tried changing the procedure so that it outputs the data in a horizontal format rather than the vertical output that you currently have?
这篇关于分割字符串后在表格中插入值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!