注意,这个是SQLServer 老版本才需要自己加这个代码, 从SQLServer 2016开始,官方已经加入字符串分割函数且效率是高于自己写的函数的
示例:
一、SPLIT函数
CREATE FUNCTION [dbo].[SPLIT] ( @StringToSplit VARCHAR(MAX),@SplitDelim VARCHAR(10) ) RETURNS @SplitStringTable TABLE (KpiCode nvarchar(MAX) NOT NULL) AS BEGIN DECLARE @SplitEndPos int DECLARE @SplitValue nvarchar(MAX) DECLARE @SplitStartPos int = 1 SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) WHILE @SplitEndPos > 0 BEGIN SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos)) INSERT @SplitStringTable (KpiCode) VALUES (@SplitValue) SET @SplitStartPos = @SplitEndPos + 1 SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) END SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647) INSERT @SplitStringTable (KpiCode) VALUES(@SplitValue) RETURN END
示例:
select * from [dbo].[SPLIT]('a,b,c',',')
二、SPLITSort
CREATE FUNCTION [dbo].[SPLITSort] ( @StringToSplit VARCHAR(MAX),@SplitDelim VARCHAR(10) ) RETURNS @SplitStringTable TABLE (KpiCode nvarchar(MAX) NOT NULL,Sort int) AS BEGIN DECLARE @SplitEndPos int DECLARE @SplitValue nvarchar(MAX) DECLARE @SplitStartPos int = 1 DECLARE @sort int=0; SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) WHILE @SplitEndPos > 0 BEGIN SET @sort=@sort+1; SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos)) INSERT @SplitStringTable (KpiCode,Sort) VALUES (@SplitValue,@sort) SET @SplitStartPos = @SplitEndPos + 1 SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) END SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647) SET @sort=@sort+1; INSERT @SplitStringTable (KpiCode,Sort) VALUES(@SplitValue,@sort) RETURN END
示例:
select * from [dbo].[SPLITSort]('a,b,c',',')