Delimiter for string data - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Delimiter for string data

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-20 : 11:08:51

Guys,

I have scenario where in a column - col004 I have following data

col001 col004
______________________________________________________
1 D,HAINESVILLE CEMETERY,,I,SANDYSTON TOWNSHIP,,

Each time after every 3rd delimiter it is populated with 'D' or 'I', the subsequent data (for next 2 delimiters)
should be inserted in another table as seperate row.

col001 col004
______________________________________________________
1 D,HAINESVILLE CEMETERY,,
1 I,SANDYSTON TOWNSHIP,,

For the same scenario I have written a stored procedure which uses cursor and function - which does the logic for the inserts but I am stuck at the loop. To pinpoint I am stuck at while (dbo.gettoken(@col004, ',', ??) = 'D' OR dbo.gettoken(@col004, ',', ??) = 'I'). I do not know what value should be 'token' which should be 1, 4, 7, 10. Each time when we encouter 'D' or 'I', I insert into different table as seperate row until we find next 'D' or 'I'.

The function when I pass string to be parsed, delimiter and token number it gives me the string next to the token number.

For example dbo.gettoken ('D,HAINESVILLE CEMETERY,,', ',', 1) gives D
dbo.gettoken ('D,HAINESVILLE CEMETERY,,', ',', 2) gives HAINESVILLE CEMETERY

CREATE PROCEDURE P_party aS
begin
declare C_partytwo cursor for
select id, col004 from maps
order by id

declare
@counter int,
@col004 varchar(400),
@col004_prev varchar(400),
@id int,
@id_prev int,
@stmt Nvarchar(400),
@fetchcount int

set @counter=0
set @col004=''
set @id=0
set @col004_prev=0
set @id_prev=0
set @fetchcount = 0

open C_partytwo
fetch next from C_partytwo into @id, @col004

while @@fetch_status<>-1
BEGIN
while while (dbo.gettoken(@col004, ',', ??) = 'D' OR dbo.gettoken(@col004, ',', ??) = 'I')
-- logic for inserting into different table

INSERT INTO PARTY VALUES (@ID, DBO.GETTOKEN(@COL004, ',', ??))

set @counter=1
set @fetchcount=@fetchcount+1
fetch next from C_partytwo into @id, @col004

-- print 'fetchcount: ' + cast(@fetchcount as varchar)
-- PRINT 'SEQ_KEY: ' + cast(@seq_key as varchar)

end
close c_partytwo
deallocate c_partytwo
end

Any suggestions and inputs would help.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 12:39:25

Also asked here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=392102

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources