創建自定義标量函数,分割字符串獲取字符串總個數
create function [dbo].[Get_StrArrayLength]
(
@str varchar(5000), --要分割的字符串
@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
創建自定義标量函数,根據分割符號和索引獲取值。
create function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(5000), --要分割的字符串
@split varchar(10), --分隔符號
@index int --取第幾個元素
)
returns varchar(5000)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
return substring(@str,@start,@location-@start)
end
字符串:14,+86,13543040626,123||12,+853,12345678,123
CREATE PROCEDURE [dbo].[app_insert_AccountSmsPhone]
@SmsPhone nvarchar(100)
AS
BEGIN
BEGIN TRAN
declare @num int
declare @smsAccess varchar(50)
declare @smsType varchar(10)
declare @smsMobile varchar(50)
declare @smsRemark varchar(200)
set @num =0
while(@num<dbo.Get_StrArrayLength(@SmsPhone,'||'))
begin
set @num = @num+1
SET @smsAccess = dbo.Get_StrArrayStrOfIndex( dbo.Get_StrArrayStrOfIndex(@SmsPhone,'||',@num),',',1)
SET @smsType =dbo.Get_StrArrayStrOfIndex( dbo.Get_StrArrayStrOfIndex(@SmsPhone,'||',@num),',',2 )
SET @smsMobile =dbo.Get_StrArrayStrOfIndex( dbo.Get_StrArrayStrOfIndex(@SmsPhone,'||',@num),',',3)
SET @smsRemark =dbo.Get_StrArrayStrOfIndex( dbo.Get_StrArrayStrOfIndex(@SmsPhone,'||',@num),',',4)
INSERT INTO account_sms (accountID, smsType, smsMobile, smsAccess, smsRemark) VALUES (@accountID,@smsType,@smsMobile,@smsAccess,@smsRemark)
if(@@error<>0) begin
rollback tran
select 0
return
end
end
COMMIT TRAN
select 1 as count
END
GO