SQL Server - Equivalent split function

I have a comma seperated value in a varchar(4000) variable
I have to get it one by one and use it
How can i do it?
e.g.

DECLARE @str varchar(4000) = 'Item1, Item2, Item3, Item4'
1.   SET @item_id = Get fist comma value from @str i.e. Item1
2.      SELECT @item_name = @item_name + Table1.Item_Name 
         FROM Table1 
         WHERE Table1.Item_Id = @item_id
3.   GO to Step 2 until all comma seperated values are parsed.

Thnx in advance

try out this function to get the comma seperated string in the form of table and iterate it in the loop to get each value

FUNCTION getstringintableform (tempstr IN VARCHAR2, separator IN CHAR)

RETURN getstringintableform_temptab

IS

temp getstringintableform_tempobj;

tempstr_ VARCHAR2 (32000) := tempstr;

separator_ CHAR (1) := separator;

separator_position NUMBER (10, 0);

:= getstringintableform_temptab

();

BEGIN

tempstr_ := tempstr_ || separator_;

separator_position := INSTR (tempstr_, separator_);

WHILE separator_position <> 0

LOOP

temp := getstringintableform_tempobj (NULL);

temp.col1 := TRIM (SUBSTR (tempstr_, 1, separator_position - 1));

tempstr_ := SUBSTR (tempstr_, separator_position + 1);

separator_position := INSTR (tempstr_, separator_);

tmptab.EXTEND;

tmptab (tmptab.COUNT) := temp;

END LOOP;

RETURN tmptab;

END getstringintableform;

Its in oracle, convert to sqlserver code...

try the below code,

DECLARE @OrderList varchar(4000)
DECLARE @OrderID varchar(10), @Pos int

 SET @OrderList = 'Item1, Item2, Item3, Item4'

 SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
 SET @Pos = CHARINDEX(',', @OrderList, 1)

 IF REPLACE(@OrderList, ',', '') <> ''
 BEGIN
  WHILE @Pos > 0
  BEGIN
   SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
   IF @OrderID <> ''
   BEGIN
    --the below statement will display the values one by one , you can manipulate the value here however you want
    SELECT @OrderID
   END
   SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
   SET @Pos = CHARINDEX(',', @OrderList, 1)

  END
 END 

I took the code from a site and modified it a bit, hope it helps! you can get lot of sample snippets when you google for it, just FYI.

You can also convert your comma-separated value into a table using the function described in the following link:

http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

With a table, you can easily do a join with other tables instead of processing your values one at a time.

IF REPLACE(@OrderList, ',', '') <> ''

Can you tell me what this line doing here?
Is All the commas in @OrderList replaced by empty string.

Some time before I use this code with few changes but don't use this line and the code worked well.

Select SUBSTRING( Names, n.seq, CHARINDEX( ',', names + ',', n.seq ) - n.seq )

from ( Select 'jatinder,raj,rajinder' Names ) XY, Numbers n

where SubString(','+Names,n.seq,1) = ',' and n.seq<len(XY.Names)

The condition of  n.seq<len(XY.Names) is there to check only ',' equal to length of string to be parsed.

With Warm regards

Jatinder Singh

http://jatindersingh.blogspot.com