SQL Server string substring with d iterations

I have a string:

@TempCol  =  sigma_x1,sigma_x2,...,sigma_xd,XX,YY,ZZ

I want to get a substring to get the string sigma_x1,sigma_x2,...,sigma_xd.

d is a variable, so it can be 1, 3, 20, ..., etc.

I know the value of d, but what I don't know is how to get the substring of the original string with d terms.

I tried this:

@L = ''
SET @ColumnNo = 0
WHILE @ColumnNo <= @d
BEGIN
    SET @L  =  @L + ' ' + SUBSTRING(@TempCol, 1, CHARINDEX(',',@TempCol)-1 )
    SET @TempCol    = REPLACE  ( @TempCol,  LTRIM(RTRIM(@L) ) ,'')
   开发者_运维技巧 Set @ColumnNo   = @ColumnNo + 1 
    PRINT @L
END 

but I do not know how to get the expected result.

DECLARE @TempCol varchar(max), @d int, @p int, @Result varchar(max);
SET @TempCol = 'item1,item2,itemA,itemB,item#,item$';
SET @d = 3;

SET @p = 1;

WHILE @d > 0 AND @p > 0 BEGIN
  SET @p = CHARINDEX(',', @TempCol, @p);
  IF @p > 0 SET @p = @p + 1;
  SET @d = @d - 1;
END;

IF @p = 0
  SET @Result = @TempCol
ELSE
  SET @Result = SUBSTRING(@TempCol, 1, @p - 2);

SELECT @Result;

Basically, the loop just searches for the final position to cut at. The substring is extracted after the loop.

If you specify too large @d then the result will simply be all of @TempCol, otherwise you get the desired number of items.

What you need is a split function (shown at the bottom).

With SplitItems As
    (
    Select Position, Value
        , Row_Number() Over ( Order By Position ) As ItemNum
    From dbo.udf_Split( @TempCol, ',' )
    )
Select Value
From SplitItems
Where ItemNum <= @d

If you want the assembled string up to a given point you would simply do:

With SplitItems As
    (
    Select Position, Value
        , Row_Number() Over ( Order By Position ) As ItemNum
    From dbo.udf_Split( @TempCol, ',' )
    )
Select ',' + Value
From SplitItems
Where ItemNum <= @d
Order By ItemNum
For Xml Path('')

Split function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[udf_Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value <= DataLength(CL.List) / 2
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )