SQL Server Split函数，将一串字符串返回成table

Posted on | Post modified: | Visitors:

写法一：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 CREATE FUNCTION [dbo].[Split]
 (
     @str      VARCHAR(MAX),        --传进来的字符串
     @char     VARCHAR(2000)        --分割符
 )
 RETURNS @t TABLE        --定义一个虚拟表 
         (
             id INT IDENTITY(1, 1),
             NAME VARCHAR(MAX)
         )
 AS
 
 BEGIN
     -- 给分割符号@char前后加上 % 即 该字符串在@str里第一次出现的位置，起始值从1开始
     -- patindex用法详见：http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263982.html
     SET @char = '%' + @char + '%'
    IF (PATINDEX(@char, @str) = 0)    --如果在@str中没有出现 @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

写法二：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

用法：SELECT NAME FROM Split('1,2,3,4,5,6,7,8,9,10',',')
结果：
SplitFunc