String parsing in Cursor function

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

SQL Server 2005 Forums

Transact-SQL (2005)

String parsing in Cursor function

Author

Topic

casecito
Starting Member

2 Posts

Posted - 2009-08-29 : 13:08:19

Can anyone explain to me why the procedure below stays in the first row of the cursor? I'm trying to parse through a string that is comma delimited and so I am inserting into a temp table and then trying to use the cursor to open each row individually and perform the parsing. When I run it...even if there is only one row in the table it never exits the cursor even though the parsing is complete. If I run it with more than one table value it never exits the first value to move on to the next.

create table #tempbroadcast (Data varchar(800))

insert #tempbroadcast (data)

select top 10 substring(raw_data,64,len(raw_data))+','
from broadcast_incoming
where header_type = 'g'
and processed = 'y'
order by key_data desc

create table #tempTest (col1 varchar(100))

Declare @pos int
Declare @piece varchar(800)
Declare @string varchar(800)

Declare broadcursor cursor for
select data
from #tempbroadcast

Open broadcursor

BEGIN
FETCH NEXT FROM broadcursor INTO @string
While @@FETCH_STATUS = 0
begin
-- if right(rtrim(@string),1) <> ','
-- set @string = @string + ','
set @pos = patindex('%,%',@string)
while @pos <> 0
begin
set @piece = left(@string, @pos-1)
set @string = stuff(@string,1,@pos,'')
set @pos = patindex('%,%',@string)
insert #tempTest (col1)
select left(@piece,(charindex(':',@piece)-1))
end
end
END

close broadcursor
deallocate broadcursor

--select col1
--from #tempTest
--select data
--from #tempbroadcast

drop table #temptest
drop table #tempbroadcast

casecito
Starting Member

2 Posts

Posted - 2009-08-29 : 13:37:51

[quote]Originally posted by casecito

Can anyone explain to me why the procedure below stays in the first row of the cursor? I'm trying to parse through a string that is comma delimited and so I am inserting into a temp table and then trying to use the cursor to open each row individually and perform the parsing. When I run it...even if there is only one row in the table it never exits the cursor even though the parsing is complete. If I run it with more than one table value it never exits the first value to move on to the next.

create table #tempbroadcast (Data varchar(800))

insert #tempbroadcast (data)

select top 10 substring(raw_data,64,len(raw_data))+','
from broadcast_incoming
where header_type = 'g'
and processed = 'y'
order by key_data desc

create table #tempTest (col1 varchar(100))

Declare @pos int
Declare @piece varchar(800)
Declare @string varchar(800)

Declare broadcursor cursor for
select data
from #tempbroadcast

Open broadcursor

BEGIN
FETCH NEXT FROM broadcursor INTO @string
While @@FETCH_STATUS = 0
begin
-- if right(rtrim(@string),1) <> ','
-- set @string = @string + ','
set @pos = patindex('%,%',@string)
while @pos <> 0
begin
set @piece = left(@string, @pos-1)
set @string = stuff(@string,1,@pos,'')
set @pos = patindex('%,%',@string)
insert #tempTest (col1)
select left(@piece,(charindex(':',@piece)-1))
end
end
END

close broadcursor
deallocate broadcursor

--select col1
--from #tempTest
--select data
--from #tempbroadcast

drop table #temptest
drop table #tempbroadcast

Here is an example of a row in the temp table that is being parsed

05064759AB:00,05067689AG:01,95494930:01,8322889111AA:01,050404231AV:01,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-29 : 16:15:57

You might have better luck with this technique:

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

If you're using SQL Server 2005 or higher you can use a CLR function to parse CSVs and other delimited strings more efficiently. The code is included in the samples that can be downloaded. Books Online has details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources