新增文章

文章标题分类内容

Select * From dbo.fn_SplitStringToTable('UserId VARCHAR(50)|UserName VARCHAR(50)', '|')  --【拆分成 数据表】
/*
   FSequence   FValue                FPosition   FLength
   1           UserId VARCHAR(50)    1           18
   2           UserName VARCHAR(50)  20          20
*/

sp_helptext fn_SplitStringToTable

/******************************************************************************  
 * Function Name: fn_SplitStringToTable                                       *  
 * Created by:    Michael .H                                                  *  
 * Creation date: 2005-05-31                                                  *  
 * Description:   Split a string to a table, simulate vb split function       *  
 * Parameters:    @InputString - Input string                                 *  
 *                @Delimeter - Delimeter                                      *  
 ******************************************************************************/  
CREATE FUNCTION fn_SplitStringToTable  
(  
    @InputString VARCHAR(8000),  
    @Delimeter   CHAR(1)  
)  
RETURNS @Table TABLE  
(  
    FSequence INT IDENTITY(1, 1),  
    FValue    VARCHAR(511),  
    FPosition INT,  
    FLength   INT  
)  
BEGIN  
    DECLARE @Position INT  
      
    SELECT @Position = 1  
      
    IF RIGHT(@InputString, LEN(@Delimeter)) <> @Delimeter  
        SET @InputString = @InputString + @Delimeter  
      
    WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR  
          SUBSTRING(@InputString, @Position, 1) = CHAR(13)  
    BEGIN  
        SELECT @Position = @Position + 1  
    END  
      
    WHILE @Position <= CHARINDEX(@Delimeter, @InputString, @Position)  
    BEGIN  
        INSERT INTO @Table(FValue, FPosition, FLength)  
             VALUES (RTRIM(LTRIM(SUBSTRING(@InputString,  
                                           @Position,  
                                           CHARINDEX(@Delimeter,  
                                                     @InputString,  
                                                     @Position) - @Position))), @Position, CHARINDEX(@Delimeter, @InputString, @Position) - @Position)  
        SELECT @Position = CHARINDEX(@Delimeter, @InputString, @Position) + 1  
          
        WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR  
              SUBSTRING(@InputString, @Position, 1) = CHAR(13)  
        BEGIN  
            SELECT @Position = @Position + 1  
        END  
    END  
    RETURN  
END