字符串转换为表的几种方式

1.使用XQuery + OUTER APPLY，方便简洁，sql server 2005及以上使用。

SELECT  T1.id
FROM    ( SELECT    p = CONVERT(XML, '<id>' + REPLACE('1,2,3,4,5,6', ',',
                                                      '</id><id>') + '</id>')
        ) T
        OUTER APPLY ( SELECT    id = TB.id.value('.', 'int')
                      FROM      T.p.nodes('/id') TB ( id )
                    ) T1;

 

2.使用字符串函数，需要对@str重新赋值

DECLARE @TB TABLE ( [id] VARCHAR(10) );
DECLARE @i INT ,
    @str NVARCHAR(100);
SET @str = '1,2,3,4,5';
SET @i = CHARINDEX(',', @str);
WHILE ( @i >= 0 )
    BEGIN 
        IF ( @i = 0 )
            BEGIN
                INSERT  @TB
                        SELECT  @str;
                BREAK;
            END;
        INSERT  @TB
                SELECT  LEFT(@str, @i - 1);
        SET @str = RIGHT(@str, LEN(@str) - @i);
        SET @i = CHARINDEX(',', @str);
   
    END;

SELECT  *
FROM    @TB;

3.使用identity，substring和 inner join 自连接，首尾要加分隔符，无需对@str重新赋值。

DECLARE @TB TABLE
    (
      row INT IDENTITY(1, 1) ,
      location INT
    );
DECLARE @split VARCHAR(1) ,
    @str VARCHAR(100) ,
    @i INT ,
    @Len INT;
SET @split = ',';
SET @str = ',122,21,33,42,52,6,72,81,92,';
SET @Len = LEN(@str);
SET @i = 1;
WHILE ( @i <= @Len )
    BEGIN
        INSERT  @TB
                SELECT  @i
                WHERE   SUBSTRING(@str, @i, 1) = @split;
        SET @i = @i + 1;
    END;
   
SELECT  SUBSTRING(@str, T.location + 1, T1.location - T.location - 1)
FROM    @TB T
        INNER JOIN @TB T1 ON T.row + 1 = T1.row;