按长度分割字符串表值函数

IF OBJECT_ID('dbo.[Fun_SplitByLen]') IS NOT NULL
	DROP FUNCTION dbo.[Fun_SplitByLen]
GO
-- =============================================
-- Author:		yenange
-- Create date: 2018-01-19
-- Description:	按长度分割字符串
-- =============================================
CREATE FUNCTION FUNCTION [dbo].[Fun_SplitByLen]
(
	@str NVARCHAR(MAX),
	@len INT
)
RETURNS TABLE 
AS
RETURN 
(
	WITH cte AS (
	SELECT 1 AS id FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' 
		AND sv.number BETWEEN 1 AND 500		--如果字符串很长，可以去掉。限制这个主要是为了性能
	),t2 AS (
		SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS rid  FROM cte AS a CROSS APPLY cte AS b
	)
	SELECT SUBSTRING(@str,(rid-1)*@len+1,@len) AS line FROM t2 WHERE LEN(@str)>(rid-1)*2
)