根据整数字符串列表，得到拆分后的表变量

最新推荐文章于 2023-01-10 05:37:09 发布

flyingdream123

最新推荐文章于 2023-01-10 05:37:09 发布

阅读量175

点赞数

分类专栏： Sql

本文链接：https://blog.csdn.net/flyingdream123/article/details/79572136

版权

Sql 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

GO
/****** Object:  UserDefinedFunction [dbo].[f_splitstring]    Script Date: 03/15/2018 18:30:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    IvanSong
-- Create date: 2013-05-29
-- Description: 获取字符串按指定分隔符分拆成表字符串
-- =============================================
ALTER function [dbo].[f_splitstring]
(
  @p_source nvarchar(4000),
  @p_mark nvarchar(1)
) 
returns @temp table([ID] int identity (1,1),[result] varchar(50) COLLATE Database_Default)
as
begin
  declare @splitsource varchar(4000), @strindex int;
  set @splitsource = ltrim(rtrim(@p_source));
  set @strindex=charindex(@p_mark,@splitsource)

  while (@strindex>=1)
  begin
	  insert @temp values (left(@splitsource,@strindex-1));
	  set @splitsource=Right(@splitsource,len(@splitsource)-@strindex+1-len(@p_mark));
	  set @strindex=charindex(@p_mark,@splitsource);
  end;

  if (@splitsource<>'')
     insert @temp values(@splitsource);
  return;
end

调用：

declare @FactoryIDTable table(id int);
    if (@FactoryIds='-1')
    insert into @FactoryIDTable select id from factory;
  else
	insert into @FactoryIDTable select convert(int,result) from dbo.f_splitstring(@FactoryIds,',');