字符串分割函数

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        
-- Create date: 2011-11-22
-- Description:    字符串分割函数
-- @Input:要进行分割的字符串
-- @Separator：分隔符
-- @RemoveEmptyEntries：是否移除空格
-- =============================================
CREATE FUNCTION [dbo].[SplitString]
    (
      @Input NVARCHAR(MAX) , --input string to be separated    
      @Separator NVARCHAR(MAX) = ',' , --a string that delimit the substrings in the input string    
      @RemoveEmptyEntries BIT = 1 --the return value does not include array elements that contain an empty string
    )
RETURNS @Table TABLE
    (
      [Id] INT IDENTITY(1, 1) ,
      [Value] NVARCHAR(MAX)
    )
AS 
    BEGIN
        DECLARE @Index INT ,
            @Entry NVARCHAR(MAX)    
        SET @Index = CHARINDEX(@Separator, @Input)
        WHILE ( @Index > 0 ) 
            BEGIN        
                SET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index - 1)))                

                IF ( @RemoveEmptyEntries = 0 )
                    OR ( @RemoveEmptyEntries = 1
                         AND @Entry <> ''
                       ) 
                    BEGIN                
                        INSERT  INTO @TABLE
                                ( [Value] )
                        VALUES  ( @Entry )            
                    END        

                SET @Input = SUBSTRING(@Input,
                                       @Index + DATALENGTH(@Separator) / 2,
                                       LEN(@Input))        
                SET @Index = CHARINDEX(@Separator, @Input)    
            END  
        SET @Entry = LTRIM(RTRIM(@Input))    
        IF ( @RemoveEmptyEntries = 0 )
            OR ( @RemoveEmptyEntries = 1
                 AND @Entry <> ''
               ) 
            BEGIN            
                INSERT  INTO @TABLE
                        ( [Value] )
                VALUES  ( @Entry )        
            END    
        RETURN 
    END
GO

使用方法：

SELECT  *
FROM    [dbo].[SplitString]('1,2,3', ',', 1)