sql 分割字符串，循環插入

来源：互联网发布：电脑锣编程群编辑：程序博客网时间：2024/05/22 12:46

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

create function [dbo].[Get_StrArrayLength](  @str varchar(5000),  --要分割的字符串  @split varchar(10)   --分隔符號)returns intasbegin  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 @lengthend

創建自定義标量函数，根據分割符號和索引獲取值。

create function [dbo].[Get_StrArrayStrOfIndex](  @str varchar(5000),  --要分割的字符串  @split varchar(10),  --分隔符號  @index int           --取第幾個元素)returns varchar(5000)asbegin  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)ASBEGIN    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 countENDGO

0 0