分割字符串

USE [SoyErp]
GO
/****** Object:  UserDefinedFunction [dbo].[Get_StepCode_Tab]    Script Date: 2017/4/22 21:55:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER  FUNCTION [dbo].[Get_StepCode_Tab]
    (
      @StepCode VARCHAR(4000) -- nvarchar(4000)工序字符串
    )
RETURNS @SplitStringsTable TABLE
    (
      [id] INT ,
      [Value] VARCHAR(800)
    )
AS 
    BEGIN
    ---***分割工序+++++++++++++++++++++++++++++++++++++++++++++***-
        DECLARE @status VARCHAR(500) ;
        DECLARE @status2 VARCHAR(500) ;
        DECLARE @N INT ;
        DECLARE @SN INT ;
        SET @StepCode=REPLACE(@StepCode,' ','')
        SET @N = LEN(@StepCode) - LEN(REPLACE(@StepCode, ',', '')) 
        SET @SN = 1     
        WHILE @SN <= @N 
            BEGIN          
                SET @status2 = LEFT(@StepCode, CHARINDEX(',', @StepCode) - 1) 
    
                INSERT  INTO @SplitStringsTable
                        ( id, Value )
                        SELECT  @SN ,
                                @status2
                SELECT  @SN = @SN + 1 ,
                        @StepCode = STUFF(@StepCode, 1,
                                          CHARINDEX(',', @StepCode), '')
            END
        INSERT  INTO @SplitStringsTable
                ( id, Value )
                SELECT  @SN ,
                        @StepCode    
        RETURN ;
    END

功能实现：“A,B,C,D" 

SELECT * FROM  dbo.[Get_StepCode_Tab]('A,B,C,D')

结果 id Value
1 A
2 B
3 C
4 D