CREATE FUNCTION [dbo].[Split]
 (
     @str      VARCHAR(MAX),        --傳進(jìn)來的字符串
     @char     VARCHAR(2000)        --分割符
 )
 RETURNS @t TABLE        --定義一個(gè)虛擬表 
         (
             id INT IDENTITY(1, 1),
             NAME VARCHAR(MAX)
         )
 AS
 
 BEGIN
     -- 給分割符號(hào)@char前后加上 % 即 該字符串在@str里第一次出現(xiàn)的位置，起始值從1開始
     -- patindex用法詳見：http://www.rzrgm.cn/moss_tan_jun/archive/2011/11/26/2263982.html
     SET @char = '%' + @char + '%'
    IF (PATINDEX(@char, @str) = 0)    --如果在@str中沒有出現(xiàn) @char，則直接往虛擬表插入@str
    BEGIN
        INSERT INTO @t
        VALUES
          (
            @str
          )
        RETURN
    END
    ELSE
    BEGIN
        DECLARE @s VARCHAR(MAX)
        SET @s = ''
        WHILE (PATINDEX(@char, @str) > 0)
        BEGIN
            SET @s = SUBSTRING(@str, 0, PATINDEX(@char, @str))
            INSERT INTO @t
            VALUES
              (
                @s
              )
            SET @str = SUBSTRING(
                    @str,
                    PATINDEX(@char, @str) + LEN(REPLACE(@char, '%', '')),
                    LEN(@str) -PATINDEX(@char, @str) + 1
                )
        END
        IF (LEN(@str) > 0 AND PATINDEX(@char, @str) = 0)
        BEGIN
            INSERT INTO @t
            VALUES
              (
                @str
              )
        END
    END
    RETURN
END

CREATE FUNCTION [dbo].[Split]
    (
      @convertString VARCHAR(MAX) ,
      @strSeprate VARCHAR(20)
    )
RETURNS @temp TABLE ( NAME VARCHAR(MAX) )
AS 
    BEGIN 
        DECLARE @i INT
        SET @convertString = RTRIM(LTRIM(@convertString))
        SET @i = CHARINDEX(@StrSeprate, @convertString)
        WHILE @i >= 1 
            BEGIN 
                INSERT @temp VALUES ( LEFT(@convertString, @i - 1) )
                SET @convertString = SUBSTRING(@convertString, @i + 1, LEN(@convertString) - @i)
                SET @i = CHARINDEX(@StrSeprate, @convertString) 
            END 
        IF @convertString <> '\' 
            INSERT @temp VALUES ( CONVERT(VARCHAR(10), @convertString) ) 
        RETURN 
    END