Sql Server实现split函数。对字符串按照特定字符串进行拆分。

sql没有split函数，因此需要实现一个函数来实现按照特定符号对字符串进行拆分。上码：

 
GO
/****** Object:  UserDefinedFunction [dbo].[SPLIT]    Script Date: 2020/4/22 9:59:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
	名称：SPLIT
	功能描述：拆分字符串
	参数：		
		@SourceSql NVARCHAR(4000),		目标字符串
		@StrSeprate NVARCHAR(10),		间隔字符串
	返回值：
		@temp TABLE(sl NVARCHAR(200))	数据表
	算法：	
	示例：select * FROM dbo.SPLIT('A,B,C,D',',') 返回数据表
		A
		B
		C
		D
	创建时间：2006-10-23
	修改：
	修改时间：
*/

CREATE  FUNCTION [dbo].[SPLIT](@SourceSql VARCHAR(max),@StrSeprate NVARCHAR(10))
RETURNS @temp TABLE(sl NVARCHAR(200))
AS 
BEGIN
	DECLARE @i INT
	SET @SourceSql=RTRIM(LTRIM(@SourceSql))
	SET @i=CHARINDEX(@StrSeprate,@SourceSql)
	WHILE @i>=1
	BEGIN
		INSERT @temp VALUES(LEFT(@SourceSql,@i-1))
		SET @SourceSql=SUBSTRING(@SourceSql,@i+1,LEN(@SourceSql)-@i)
		SET @i=CHARINDEX(@StrSeprate,@SourceSql)
	END
	INSERT @temp VALUES(@SourceSql)
	RETURN 
END
 
GO

使用：

SELECT  *from  [dbo].[SPLIT] ('1001,1002,1003',',') 

------------------------------------------------扩展一点----------------------------------------------

传入的是code的拼接，要求返回name的拼接；例如：输入值为“1001,1002,1003”，返回为“哈哈哈，嘿嘿嘿，啦啦啦”；

GO

/****** Object:  UserDefinedFunction [dbo].[FuncCompanySplite]    Script Date: 2020/4/22 10:04:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[FuncCompanySplite]
(
	@SourceSql VARCHAR(max)
)
returns nvarchar(max)
AS
BEGIN
	DECLARE @cropId nvarchar(50)
	DECLARE @companyNm nvarchar(200)
	DECLARE @companyNms nvarchar(max)
	DECLARE @i INT
	set @SourceSql = @SourceSql+','
	SET @SourceSql=RTRIM(LTRIM(@SourceSql))
	SET @i=CHARINDEX(',',@SourceSql)
	WHILE @i>=1
	BEGIN
	set @cropId  =(LEFT(@SourceSql,@i-1))	
	select @companyNm=companyNm from DV_Company where companyId=@cropId;
		if @companyNms is null
		begin
		 set @companyNms = @companyNm
		end
		 else begin  
		 set @companyNms = @companyNms +','+ @companyNm
		 end
		SET @SourceSql=SUBSTRING(@SourceSql,@i+1,LEN(@SourceSql)-@i)
		SET @i=CHARINDEX(',',@SourceSql)
	END
 
	    RETURN  @companyNms
	 
END
GO