sql 分割字符串，循環插入

創建自定義标量函数，分割字符串獲取字符串總個數

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