get specific part of a string, by index?

I have a string like:

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

So how could I get a specific part of that string, based on, lets say an index. so

• if index is 0, get sigma_x1
• if index is 1, get sigma_x2
• if index is 2, get sigma_x3
• if index is d-1,get sigma_xd
• if index is d, get XX,
• if index is d+1,get YY and so on.

Previously Andriy M solved a similar issue, his code gets a su开发者_如何学运维bstring based on a nomber but returns a substring the following way:

• if @d is 1,get sigma_x1
• if @d is 2,get sigma_x1,sigma_x2
• if @d is 3,get sigma_x1,sigma_x2,sigma_x3
• if @d is 4,get sigma_x1,sigma_x2,sigma_x3,sigma_x4
• if @d is d,get sigma_x1,sigma_x2,sigma_x3,sigma_x4,...,sigma_xd (ALL THE STRING)

How to update this procedure to get specific element?

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;

---

just try this. hope this will meet your needs.

create a function GetIndex, which accepts string and delimiter to split the string

CREATE FUNCTION dbo.GetIndex(@String varchar(8000), @Delimiter char(1))       
 returns @temptable TABLE (itemindex int identity(1,1), items varchar(8000))       
 as       
 begin       
     declare @idx int       
     declare @slice varchar(8000)       

     select @idx = 1       
         if len(@String)<1 or @String is null  return       

     while @idx!= 0       
     begin       
         set @idx = charindex(@Delimiter,@String)       
         if @idx!=0       
             set @slice = left(@String,@idx - 1)       
         else       
             set @slice = @String       

         if(len(@slice)>0)  
             insert into @temptable(Items) values(@slice)       

         set @String = right(@String,len(@String) - @idx)       
         if len(@String) = 0 break       
     end   
 return       
 end 

and you can query like,
suppose you need 4th index then

select * from dbo.GetIndex(@TempCol,',') where itemindex = 4

to get an item of 4th index then

select items from dbo.GetIndex(@TempCol,',') where itemindex = 4

to get item to a variable

select @Aux = items from dbo.GetIndex(@TempCol,',') where itemindex = 4