sql字符串截取

来源：互联网发布：外国人评价道德经知乎编辑：程序博客网时间：2024/05/21 15:49

//分割字符串

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_StrArrayLength]') and xtype in (N'FN', N'IF', N'TF'))  begin    drop function [dbo].[Get_StrArrayLength]  endGOCREATE function [dbo].[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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_StrArrayStrOfIndex]') and xtype in (N'FN', N'IF', N'TF'))  begin    drop function [dbo].[Get_StrArrayStrOfIndex]  endGOCREATE function [dbo].[Get_StrArrayStrOfIndex]( @str varchar(1024),  --要分割的字符串 @split varchar(10),  --分隔符号 @index int --取第几个元素)returns varchar(1024)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 --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。 return substring(@str,@start,@location-@start)endgo

declare @id int,@periodRecord varchar(1024)          set @id = (select top(1) id from @Ids)           --用货记录 [格式:'1,53,55,88']          set @periodRecord = (select PeriodRecord from customer where Id = @id)                    --调用自定义方法,将记录拆分存放到临时表            declare @next int declare @temp table(id int) delete from @temp--判断记录数是否大于次数  if(dbo.Get_StrArrayLength(@periodRecord,',') >@times)begin   set @next=dbo.Get_StrArrayLength(@periodRecord,',')-@times+1end  elsebegin   set @next =2 --PeriodRecord 默认值为0end  while @next<=dbo.Get_StrArrayLength(@periodRecord,',') begin Insert into @temp select dbo.Get_StrArrayStrOfIndex(@periodRecord,',',@next) set @next=@next+1 end