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
)
按长度分割字符串表值函数
最新推荐文章于 2021-04-12 14:25:34 发布