logo

Equivalent split function

Posted by Umair Imran in SQL Server

Tuesday, March 04, 2008 5:06:11 AM

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


Biography
Umair has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor
egg egg

Equivalent split function

egg egg replied to Umair Imran

EggHeadCafe's human moderators scored this post for our messageboard contest.

Tuesday, March 04, 2008 5:22:05 AM

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);

tmptab getstringintableform_temptab

:= 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...

 


Biography
Having around 7 years of experience in designing and implementing software products including windows, console, service oriented and ofcourse predominantly on Web with .Net, XML, Javascript, Oracle, SQL Server For any technical queries, just leave a message in santhosh.hyd.tech@gmail.com

Site Rank:  7th place - Current Winnings:  $71.00

Reply Reply Using Power Editor

parsing the comma seperated values

sundar k replied to Umair Imran

EggHeadCafe's human moderators scored this post for our messageboard contest.

Tuesday, March 04, 2008 5:26:49 AM

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.


Biography
sundar has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor

Comma Delimited Value to Table

F Cali replied to Umair Imran

EggHeadCafe's human moderators scored this post for our messageboard contest.

Tuesday, March 04, 2008 9:44:10 AM

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.


Biography
How well do you know SQL? Find out with the free test assessment from SQL Server Helper:
http://www.sql-server-helper.com/free-test/default.aspx

Site Rank:  5th place - Current Winnings:  $94.00

Reply Reply Using Power Editor

One Question

Muhammad Deeraf replied to sundar k

EggHeadCafe's human moderators scored this post for our messageboard contest.

Tuesday, March 04, 2008 11:07:14 PM

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.


Biography
Muhammad has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor

comma value alone

sundar k replied to Muhammad Deeraf

EggHeadCafe's human moderators scored this post for our messageboard contest.

Wednesday, March 05, 2008 4:36:48 AM

incase if your @OrderList variable just contains the value comma (,) then we dont have to process it, thats why they are checking this IF Condition which will return '' in this case.

Biography
sundar has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor

thnx

Umair Imran replied to sundar k

Wednesday, March 05, 2008 6:53:20 AM

end of post

Biography
Umair has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor

Comma Seperated Values

Jatinder Singh replied to sundar k

Wednesday, March 05, 2008 6:57:05 AM


Hello There,

Try this , I hope it helps

Select identity(int,1,1) seq into Numbers from sys.sysobjects t ,sys.sysobjects t1

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

 

 


Biography
Jatinder has not submitted biographical details.

Site Rank:  Not applicable - Current Winnings:  $0.00

Reply Reply Using Power Editor
 

Didn't Find The Answer You Were Looking For?

EggHeadCafe has experts online right now that may know the answer to your question.  We pay them a bonus for answering as many questions as they can.  So, why not help them and yourself by becoming a member (free) and ask them your question right now?
Ask Question In Live Forum








  $1000    Adam Houldsworth - $173  |  Jonathan VH - $154  |  Kirtan Patel - $116  |  Mr. Khan - $99  |  F Cali - $94  |  more Neado  |  Free Icons  |  Privacy  |   (c) 2010