把字符串传到存储过程中分割
<div class="postText">有时候需要把多个值同时传到存储过程中去为了避免造成程序与数据库多次交互
可以把多个ID用某个符号分隔，如"1,2,3"，用一个字符串传到存储过程里
注意：把最后的那个符号去掉，应是" 1,2,3 "而不是" 1,2,3, "
创建分解函数：
/*--------------------------------------------------------------------------------------------
[描述]
分解字符串，然后将字符串存放在Table中
--------------------------------------------------------------------------------------------*/
create FUNCTION .
(
@Text NVARCHAR(4000),
@Split NVARCHAR(50) = ','
)
RETURNS @Table TABLE( INT IDENTITY(1, 1) NOT NULL, NVARCHAR(4000))
AS
BEGIN
DECLARE @Oldword NVARCHAR(4000)
DECLARE @FirstWord NVARCHAR(4000)
DECLARE @Length INT
DECLARE @CommaLocation INT
SELECT @Oldword = @Text
SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
WHILE (@CommaLocation > 0) AND (@Oldword IS NOT NULL)
BEGIN
SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
SELECT @Length = DATALENGTH(@Oldword)
SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
SELECT @Oldword = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)
INSERT INTO @Table() VALUES(@FirstWord)
SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
END
IF @Oldword IS NOT NULL
INSERT INTO @Table() VALUES(@Oldword)
RETURN
END
调用：
方法一
select * from uf_Split('1,2,3,4',',')
方法二
declare @value nvarchar(50)
set @value = 'a,b,c'
select * from uf_Split(@value,',')
然后可以遍历这个TABLE
-----------------------------------------------------------------------------------------------------------------------------
1.利用replace
create table #temp
(
ss varchar(200) not null
)
declare @str varchar(200)
declare @result varchar(1000)
set @str='aaa,bb,c,d,e,ffffff'
set @result =' insert into #temp(ss) select '''+replace(@str,',','''union select''')+''''
exec(@result)
select * from #temp
2.利用charindex和substring
create table #temp
(
ss varchar(200) not null
)
declare @str varchar(200)
declare @curr int
declare @prev int
set @str='aaa,bb,c,d,e,ffffff'
set @curr=1
set @prev=1
while @prev < len(@str)
begin
set @curr=charindex(',',@str,@prev)
if @curr>@prev
insert #temp select substring(@str,@prev,@curr-@prev)
else
begin
insert #temp select substring(@str,@prev,len(@str)-@prev+1)
break
end
set @prev=@curr+1
end
select * from #temp
页:
[1]