hi guys. thanks in advance for any help on this one. so i am trying to get this semi tricksy loop running in ASE12.5 natively without having to resort to writing a perl script to do the work. let me show you an example of the table: filename routeids xxxx 1;2 yyyy 3;1;2;4;5;6 zzzz NULL now the output of the loop i want to look something like this: NULL 1 2 3 4 5 6 so i need some sort of patindex or charindex loop that will get all the individual routeid's out of each row, and then move to the next row until it scans across the whole table. this is what i am trying so far: declare @voute varchar(20) select @voute='' While 1=1 begin select @voute=(select substring(routeids,1,(select patindex('%;%',route)-1 from TABLE where feedname = 'xxxx')) from TABLE where feedname = 'xxxx' and routeids>@voute) if @voute is null break else print @voute continue end ^ obviously the above example is me trying to get it working for a specific row 1st. it just seems to loop and loop and never break at the end of the row, so i can't get to the stage where i put the cursor in. thanks again guys this is a real stinker for me....

Just curious. When does 1 not equal 1?

my logic there was to have an always true until it gets to the end of the row, and then it breaks. i was hoping the is null clause would break the loop. it may be where the issue is...

Ok. I don't know the syntax, but it would appear that you can't get to the null value because your WHERE clause states to only look at feedname 'xxxx' and in your example above the null value has a feedname/filename of 'zzzz' Also, I believe the patindex function only returns the first occurrence; therefore, you should have an infinite loop that returns the value 1 over and over. I would get rid of the patindex function and use the substring function exclusively. Think about something like this. SET @i = 1 WHILE (@i < 10) SELECT @voute=(SELECT SUBSTRING(routeid,@i,1) FROM TABLE) PRINT @voute SET @i = @i + 2 Or SET @voute = 1 SET @i = 1 WHILE (@voute IS NOT NULL) SELECT @voute=(SELECT SUBSTRING(routeid,@i,1) FROM TABLE) PRINT @voute SET @i = @i + 2

oh yeah i did the where clause for file xxxx just tryna get one row working first. i'll try this once i have database access again thanks mdow. one thing i might have missed in the example, the routeid's may be more than one character... anywhere from 1-7 characters so i need to use the semicolon as a separator. but yeah i will look at your sql and adapt as necessary....

Hopefully you are on the right track. Just keep in mind that I did know that you were trying to loop through one record. The ideas that I gave you were intended to do the same. To accomplish both tasks, you will need a nested loop. Interesting problem with the routids. The patindex function would be useful there if you know the correct start position for each iteration of the loop. --Mike