例子一
CREATE PROCEDURE dbo.Vip_SendMails
@userids varchar(MAX),
@fromwho varchar(50),
@subject varchar(300),
@c varchar(MAX),
@split varchar(2)
AS
BEGIN
while(charindex(@split,@userids)<>0)
begin
insert into VipMail (fromwho,towho,subject,content)
values (@fromwho,substring(@userids,1,charindex(@split,@userids)-1),@subject,@c)
set @userids = stuff(@userids,1,charindex(@split,@userids),'')
end
END
GO
--调用 注意:格式要是“xxx,sss,fff,hhh,”最后一个也要加上
exec dbo.Vip_SendMails 'sada,qweqw,asdsadasds,iioo,kkkk,dddd,wqwqweqweq,','sssss','测试标题','内容内容',','
例子二
有分号之前的长度减去没分号之后的长度就是数组长度
select len('1:10:100')-len(replace('1:10:100',':',''))
例子三:
定义一个函数,在调用该函数去取值
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
--调用示例:select dbo.Get_StrArrayLength('78,1,2,3',',')
--返回值:4