split 函数

split 函数

 

CREATE FUNCTION fntest ( @list VARCHAR(MAX) )
RETURNS @t TABLE ( SecId VARCHAR(10) )
AS 
BEGIN 

     ;
WITH    cte
AS ( SELECT   SUBSTRING(@list, LEN(@list) - 10 + 1, 10) AS v
                               ,1 lvl
UNION ALL
SELECT   SUBSTRING(@list,
LEN(@list) - 10 * ( lvl + 1 ) + 1,
10) AS v
                               ,lvl + 1
FROM     cte
WHERE    LEN(@list) - 10 * ( lvl + 1 ) + 1 > 0
                     )
INSERT  INTO @t
SELECT  v
FROM    cte
OPTION  ( MAXRECURSION 0 )

RETURN
END


Go

 

CREATE FUNCTION dbo.fntest (@list VARCHAR(MAX))
RETURNS @result TABLE (Id CHAR(10)) AS

BEGIN
DECLARE @currentPosition  int
DECLARE @tempString       varchar(MAX)
DECLARE @remaining     int
DECLARE @tempValue       varchar(MAX)

SET @currentPosition = 1
SET @remaining = ''
WHILE @currentPosition <= datalength(@list)
BEGIN
SET @tempString = substring(@list, @currentPosition, 8000)
SET @currentPosition = @currentPosition + 8000

SET @remaining = len(@tempString)

WHILE @remaining > 0
BEGIN
SET @tempValue = left(@tempString, 10)
INSERT @result (Id) VALUES(LTRIM(@tempValue))        

SET @tempString = substring(@tempString, 11, len(@tempString))
SET @remaining = @remaining - 10
END
END
RETURN
END
GO

 

 

-- table creation

USE db1
go

CREATE TABLE Numbers ( n INT NOT NULL )
ALTER TABLE Numbers ADD CONSTRAINT pk_Numbers PRIMARY KEY CLUSTERED (n)


DECLARE @i INT
SET @i = 0

WHILE @i <= 8000 
BEGIN
INSERT  Numbers
SELECT  @i
SET @i = @i + 1
END


-- create function
CREATE FUNCTION dbo.fntest ( @list VARCHAR(MAX) )
RETURNS TABLE
AS
RETURN
    ( SELECT    Id = SUBSTRING(l.v n * 10 + 1 10)
FROM      ( SELECT    @list v
                ) l
INNER JOIN db1..Numbers n ON n <= LEN(l.v) / 10
WHERE     LEN(SUBSTRING(l.v n * 10 + 1 10)) > 0
    )
Go

 

 

根据性能测试，第三种方法是最优化的。

 

 

posted on 2012-03-28 11:06  Henry.Lau 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Henry1225/archive/2012/03/28/2420878.html