| Equivalent split function |
egg egg provided a rated reply to Umair Imran on Tuesday, March 04, 2008 5:22 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...
|
| Reply Reply Using Power Editor |
| 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 |
| |
Rank |
Winnings |
Points |
| February |
5 |
$94.00 |
116 |
| January |
4 |
$100.00 |
362 |
|
|
|
|
|
|
| parsing the comma seperated values |
sundar k provided a rated reply to Umair Imran on Tuesday, March 04, 2008 5:26 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.
|
| Reply Reply Using Power Editor |
| |
| |
Rank |
Winnings |
Points |
| February |
0 |
$0.00 |
0 |
| January |
0 |
$0.00 |
0 |
|
|
|
|
|
|
|
|
| One Question |
Muhammad Deeraf provided a rated reply to sundar k on Tuesday, March 04, 2008 11:07 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.
|
| Reply Reply Using Power Editor |
| |
| |
Rank |
Winnings |
Points |
| February |
0 |
$0.00 |
0 |
| January |
0 |
$0.00 |
0 |
|
|
|
|
|
|
| comma value alone |
sundar k provided a rated reply to Muhammad Deeraf on Wednesday, March 05, 2008 4:36 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. |
| Reply Reply Using Power Editor |
| |
| |
Rank |
Winnings |
Points |
| February |
0 |
$0.00 |
0 |
| January |
0 |
$0.00 |
0 |
|
|
|
|
|
|
|
|
| Comma Seperated Values |
| Jatinder Singh replied to sundar k on Wednesday, March 05, 2008 6:57 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
|
| Reply Reply Using Power Editor |
| |
| |
Rank |
Winnings |
Points |
| February |
0 |
$0.00 |
0 |
| January |
0 |
$0.00 |
0 |
|
|
|
|
|
|
|