Sergyi, you are a whiner.

I quote OP first post " From this string, I need to extract the first occurence of a non zero value, and everything to the right...

@@ROWCOUNT is highly volatile. Once referenced, it is reset.

IF @@ROWCOUNT = 0  -- Checked and reset.

INSERT ... VALUES (@@ROWCOUNT)

I just love people who take the effort to post feedback to their problems and the suggestions they get!

I just love people who take the effort to post feedback to their problems and the suggestions they get!

I just love people who take the effort to post feedback to their problems and the suggestions they get!

Why so complicated queries?

select

testid,

val,

substring(val, patindex('%[^0]%',

FOR XML PATH is available in SQL Server 2000?

Asked and answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86434

Vamshi, you can omit the LEFT JOIN part and the WHERE ... IS NULL part, if there are some contraints prohibiting circular reference in your table.

http://support.microsoft.com/kb/248915

http://msdn2.microsoft.com/en-US/library/aa172799(SQL.80).aspx

Here is a valid solution, Sergiy, so you have evidence that I not only suggest better alternatives and that I can produce the wanted result as well according to OP...

And I also have answered this question before, so OP would be able to GOOGLE and find what he want.

I tried to fit in your shoes for once, and I didn't like it.

Yes, I know. But I have never understood the idea of calculating the depth every time over and over again...

I am more interested in why the query returns a different number of records between each execution!

I can't see the point on returning 200k rows to the client.