- --先增加辅助表
- SET NOCOUNT ON
- DECLARE @i INT,@iMax INT
- SET @iMax=100000
- INSERT INTO spt_values (number) VALUES(1)
- WHILE 1=1
- BEGIN
- SELECT @i=MAX(number) FROM spt_values
- IF @i<=@imax/2
- BEGIN
- INSERT INTO spt_values (number)
- SELECT @i+number FROM spt_values ORDER BY number
- END
- ELSE
- BEGIN
- INSERT INTO spt_values (number)
- SELECT TOP( @iMax-@i ) @i+number FROM spt_values ORDER BY number
- BREAK;
- END
- END
- GO
- --创建表值函数
- IF OBJECT_ID('[dbo].[Fun_SplitStr]') IS NOT NULL
- DROP FUNCTION [dbo].[Fun_SplitStr]
- GO
- -- =============================================
- -- Author: yenange
- -- Create date: 2017-01-11
- -- Description: 字符串切分函数(以逗号为切分符)
- -- =============================================
- CREATE FUNCTION [dbo].[Fun_SplitStr]
- (
- @str NVARCHAR(MAX)
- )
- RETURNS TABLE
- AS
- RETURN
- (
- SELECT SUBSTRING(@str,b.number,CHARINDEX(',',@str + ',b.number) -b.number) AS item
- FROM dbo.spt_values b
- WHERE
- b.number BETWEEN 1 AND LEN(@str)
- AND SUBSTRING(',' + @str,1) = ','
- --AND ISNULL(SUBSTRING(@str,b.number) -b.number),'')!=''
- )
用 master..spt_values 也行, 但最大才2047, 不够用。
与xml那个切分函数对比, 效率上也没有优势。 不过也算另一种思维, 特此记之。