Using a split function within a Cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a split function within a Cursor

Author

Topic

cimmycimmy
Starting Member

1 Post

Posted - 2011-07-01 : 18:01:41

Please bear with me if this is a little lengthy. I have to report the fifth delimited value for each row for a given table. The table has things like:
MyItem Myvalue
1 one~two~three~four~five
2 five~one~two~three~four
In this case, I would report MyItems 1 and 2 above with five and four…Makes sense, then read on!!

Anyway, I decided to write a split function to extract from each row , the last delimited value. To get to each row, I wrote a cursor.
The split function works like this:-
SELECT * FROM dbo.SplitString('one~two~three~four~five','~') ORDER BY SplitString.Itemnum DESC
Itemnum Items
5 five
4 four
3 three
2 two
1 one

My problem is that when I combined the split function and the Cursor stuff, the query returns the first item in the split result set ( instead of the fifth) and these do not get printed nicely on the grid. In fact I have to cancel the query because it appears to be looping endlessly.
Here is the code to pull it off:
---First I select the data I need and put it into a temp table
SELECT Athing,History,Description INTO #tempresults FROM AthingHistory

DECLARE @MoreInfo CHAR(250),@AthingNum VARCHAR(15)
DECLARE DescCursor CURSOR FOR
SELECT Athing,Description FROM #tempresults

OPEN DescCursor;
FETCH NEXT FROM DescCursor INTO @AthingNum,@MoreInfo

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT TOP 1 items FROM dbo.SplitString(@MoreInfo,'~')
ORDER BY Itemnum DESC

FETCH NEXT FROM DescCursor INTO @AthingNum,@MoreInfo
END

CLOSE DescCursor
DEALLOCATE DescCursor
[size=6]Can someone please tell me what I am doing wrong?[/size=6

]

In case you want to see it, here is the function to split a delimited string, which I am confident works.
ALTER FUNCTION [dbo].[SplitString]
(
-- Add the parameters for the function here
@String varchar (8000),
@Delimiter char (1)
)
RETURNS @temptable TABLE (Itemnum int,Items varchar(8000))

AS

begin
declare @idx int
declare @itemnum int
declare @slice varchar(8000)

select @idx = 1
set @itemnum=0
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)
set @itemnum=@itemnum+1
insert into @temptable(Itemnum,Items) values(@itemnum,@slice)

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

end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 22:32:35

See ParseString here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-03 : 17:54:29

Cursors? non-1NF data? The real answer is to get valid DDL instead of kludges to fix it in the database. Passing a list of parameters to a stored procedure can be done by putting them into a string with a separator. I like to use the traditional comma. Let's assume that you have a whole table full of such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the original key column and one parameter per row.

It makes life easier if the lists in the input strings start and end with a comma. You will need a table of sequential numbers -- a standard SQL programming trick, Now, the query,

CREATE VIEW ParmList (keycol, place, parm)
AS
SELECT keycol,
COUNT(S2.seq), -- reverse order
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Series AS S1, Series AS S2
WHERE SUBSTRING (',' + I1.input_string + ',', S1.seq, 1) = ','
AND SUBSTRING (',' + I1.input_string + ',', S2.seq, 1) = ','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Series are used to locate bracketing pairs of commas, and the entire set of substrings located between them is extracted and cast as integers in one non-procedural step. The trick is to be sure that the right hand comma of the bracketing pair is the closest one to the first comma. The relative position of each element in the list is given by the value of "place", but it does a count down so you can plan horizontal placement in columns.

This might be faster now:

WITH Commas(keycol, comma_seq, comma_place)
AS
(SELECT I1.keycol, S1.seq,
ROW_NUMBER() OVER (PARTITION BY I1.keycol ORDER BY S1.seq)
FROM InputStrings AS I1, Series AS S1
WHERE SUBSTRING (',' || I1.input_string || ','
FROM S1.seq
FOR 1) = ','
AND S1.seq <= CHARLENGTH (I1.input_string))

SELECT SUBSTRING(',' || I1.input_string || ','
FROM C1.comma_place +1
FOR C2.comma_place - C1.comma_place - 1)
FROM Commas AS C1, Commas AS C2
WHERE C2.comma_seq = C1.comma_seq + 1
AND C1.keycol = C2.keycol;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources