学步园

常遇到根据分隔符来拆分字符串的，总结三种方法：

1.使用union all 方法

--使用union all 方法
--思路是组装成select语句的联合[SELECT '张三' union all select '李四' union all select '王五']
DECLARE @str NVARCHAR(MAX);
SET @str='张三,李四,王五';
SET @str='SELECT '+''''+ REPLACE(@str,',','''  union all select ''')+'''';
PRINT @str;
EXEC( @str);

 

2.使用临时表

 

--使用临时表
--思路是拼凑成[,张三,李四,王五,]字符串，找到每个[,]字符的位置，再通过substring函数依次截取
DECLARE @str NVARCHAR(MAX);
SET @str=N','+N'张三,李四,王五'+N',';

--创建临时表
SELECT TOP(50) IDENTITY(INT,1,1) AS col1
INTO #demo1
FROM sys.objects o,sys.objects o2

--创建字符[,]位置序列表#demo2
SELECT ROW_NUMBER() OVER (ORDER BY col1 ASC) AS ident,
     col1 INTO #demo2 FROM #demo1 
WHERE col1=CHARINDEX(',',@str,col1);

--根据[,]字符位置依次截取字符串
SELECT SUBSTRING(@str,d3.col1+1,d2.col1-d3.col1-1) FROM #demo2 d2
INNER JOIN #demo2 d3 
ON d2.ident=d3.ident+1;
DROP TABLE #demo1,#demo2;
GO

 

3.使用CTE方法解决，思路与使用临时表相同

 

--把上面的TSQL语句使用CTE联合起来使用
DECLARE @str NVARCHAR(MAX);
SET @str=N','+N'张三,李四,王五'+N','
;WITH #demo1 AS 
(
SELECT TOP(50) ROW_NUMBER() OVER (ORDER BY [object_id] ASC) AS col1
FROM sys.objects 
ORDER BY col1 ASC 
),
#demo2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY col1 ASC) AS ident,
     col1  FROM #demo1 
WHERE col1=CHARINDEX(',',@str,col1)
)
SELECT SUBSTRING(@str,d3.col1+1,d2.col1-d3.col1-1) FROM #demo2 d2
INNER JOIN #demo2 d3 
ON d2.ident=d3.ident+1;

 

总结:字符串拆分无非就这几招：临时表法,动态TSQL,循环法,游标法,公用表达式(CTE)法等总有一种最优的

 

 

2.使用辅助表和临时表

 

--使用辅助表，临时表法
--使用master..spt_values使用辅助的number列,从-2048

IF OBJECT_ID('tempdb..#t') IS NOT NULL 
DROP TABLE tempdb..#t;

CREATE TABLE  #t (col INT IDENTITY(1,1),number INT);
DECLARE @str NVARCHAR(MAX);
SET @str='张三,李四,王五';
SET @str=','+@str+',';

--创建临时表,表变量不能使用别名
INSERT  INTO #t (number)  SELECT sv.number FROM [master].dbo.spt_values sv 
WHERE TYPE='p' 
AND number= CHARINDEX(',',@str,sv.number);

SELECT SUBSTRING(@str,number+1,name_length-1) AS col FROM 
(
	SELECT t1.number, t2.number-t1.number AS name_length  FROM #t t1 INNER JOIN #t t2
	ON t1.col+1=t2.col
) AS tmp

3.使用循环截取法（网上摘录）

-- 分拆字符串- 循环截取法
CREATE FUNCTION dbo.f_splitSTR(
	@s   varchar(8000),   --待分拆的字符串
	@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(
		col varchar(100))
AS
BEGIN
	DECLARE 
		@splitlen int

	-- 取分隔符的长度, 在分隔符后面加一个字符是为了避免分隔符以空格结束时, 取不到正确的长度
	SET @splitlen = LEN(@split + 'a') - 2
	-- 如果待分拆的字符串中存在数据分隔符, 则循环取出每个数据项
	WHILE CHARINDEX(@split, @s)>0
	BEGIN
		-- 取第一个数据分隔符前的数据项
		INSERT @re VALUES(LEFT(@s, CHARINDEX(@split, @s) - 1))
		
		-- 将已经取出的第一个数据项和数据分隔符从待分拆的字符串中去掉
		SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
	END
	
	-- 保存最后一个数据项(最后一个数据项后面没有数据分隔符, 故在前面的循环中不会被处理)
	INSERT @re VALUES(@s)
	RETURN
END
GO