SQL脚本整理系列一 分隔函数

原来效果：

fName    Scroe
王某某    101,102,109
李某某    102,103
王某某    103
李某某    
李某某    101,102,103
王某某    222

执行后效果：

name    score
李某某    101
李某某    102
李某某    103
王某某    101
王某某    102
王某某    103
王某某    109
王某某    222

--基础数据表创建

IF OBJECT_ID('Test1') >0
DROP TABLE dbo.Test1

CREATE TABLE Test1
    (
      fName NVARCHAR(10) ,
      Scroe VARCHAR(50)
    );

INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'王某某', -- fName - nvarchar(10)
          '101,102,109'  -- Scroe - varchar(50)
          )
INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'李某某', -- fName - nvarchar(10)
          '102,103'  -- Scroe - varchar(50)
          )
INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'王某某', -- fName - nvarchar(10)
          '103'  -- Scroe - varchar(50)
          )
INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'李某某', -- fName - nvarchar(10)
          ''  -- Scroe - varchar(50)
          )
INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'李某某', -- fName - nvarchar(10)
          '101,102,103'  -- Scroe - varchar(50)
          )
 INSERT  dbo.Test1
        ( fName, Scroe )
VALUES  ( N'王某某', -- fName - nvarchar(10)
          '222'  -- Scroe - varchar(50)
          ) 
          
          
 SELECT * FROM Test1
          --DECLARE @Scroe VARCHAR(max)
View Code

--创建分隔函数

 1 IF OBJECT_ID('getSplitResult') > 0
 2     DROP FUNCTION getSplitResult
 3 GO
 4 
 5 /*
 6 分隔函数
 7 
 8 */
 9 
10 CREATE  FUNCTION dbo.getSplitResult
11     (
12       @SourceObject VARCHAR(MAX) ,--源对象
13       @Split VARCHAR(1) = ','--分隔参数
14     )
15     --返回一个表
16 RETURNS @tb TABLE ( score VARCHAR(10) )
17 AS
18     BEGIN 
19         --不含分隔符
20         IF CHARINDEX(@Split, @SourceObject) = 0
21             INSERT  @tb
22                     ( score )
23             VALUES  ( @SourceObject -- score - varchar(10)
24                       );
25                                   
26         WHILE ( CHARINDEX(@Split, @SourceObject) > 0 )
27             BEGIN
28                 --插入数据
29                 INSERT  @tb
30                         ( score
31                         )
32                 VALUES  ( SUBSTRING(@SourceObject, 0,
33                                     CHARINDEX(@Split, @SourceObject))  -- score - varchar(10)
34                         );
35                             
36                 SET @SourceObject = RIGHT(@SourceObject,
37                                           LEN(@SourceObject)
38                                           - CHARINDEX(@Split, @SourceObject)) 
39                                                               
40                 IF ( CHARINDEX(@Split, @SourceObject) = 0
41                      AND LEN(@SourceObject) <> 0
42                    )
43                     INSERT  @tb
44                             ( score )
45                     VALUES  ( @SourceObject -- score - varchar(10)
46                               );
47             END
48              
49         RETURN
50     END
51
View Code

--创建存储过程

IF OBJECT_ID('sp_SplitResult') > 0
    DROP PROC sp_SplitResult
GO

CREATE  PROC sp_SplitResult
AS
    BEGIN 
     --声明一个表
        SET   NOCOUNT ON
        DECLARE @tb TABLE
            (
              name VARCHAR(20) ,
              score VARCHAR(10)
            )
        DECLARE @name VARCHAR(20)= '' ,
            @SourceObject VARCHAR(MAX)
        
        --创建游标
        DECLARE cursor_tb CURSOR FAST_FORWARD
        FOR
            ( SELECT    fName ,
                        Scroe
              FROM      dbo.Test1
              WHERE     Scroe <> ''
            )
        OPEN  cursor_tb
        FETCH NEXT FROM cursor_tb
        INTO @name, @SourceObject;
        
        WHILE @@FETCH_STATUS = 0
            BEGIN 
                INSERT  @tb
                        SELECT  @name ,
                                score
                        FROM    dbo.getSplitResult(@SourceObject, ',') 
                FETCH NEXT FROM cursor_tb
        INTO @name, @SourceObject
               
                        
            END
        
        CLOSE cursor_tb
        DEALLOCATE cursor_tb
        
      
      
        SELECT DISTINCT
                *
        FROM    @tb
    END


--  EXEC sp_SplitResult
View Code

--执行结果

EXEC sp_SplitResult

 





posted @ 2015-04-07 15:26  maanshancss  阅读(294)  评论(0)  编辑  收藏  举报