Ntext Replace - UPDATETEXT for whole table 1

[toetag]

I've written the following script:

DECLARE @PtrVar BINARY(16)
DECLARE @InsertPos INT
DECLARE @DeleteLen INT

select @PtrVar = TEXTPTR(issues),
       @InsertPos = (PATINDEX('%Likelihood mark%', issues) -1),
       @DeleteLen = (
                      (PATINDEX('%mark%', issues) + 4)  - PATINDEX('%Likelihood%', issues)
                    )
from [sltds_c32_e-table])
[b]where docid = 'RD00000017'[/b]

BEGIN
	UPDATETEXT [sltds_c32_e-table].issues
        	@PtrVar
           	@InsertPos
           	@DeleteLen
           	with Log
           	'LOC [Strength of Mark]'
END

This works fine on a single record where docid = 'RD00000017' but i can't get this to loop through the table. I'm still searching the forums and faqs. Any suggestions?

Thank you in advance.

"The only desert to an Irishman is an empty glass".

 

[vongrunt]

Open loop - with cursor or incremented variable, whatever - then read primary key within a loop.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[toetag]

Sounds good in theory.

Any possible examples you can point me at?

I've looped it now, counter that goes up to 220 (only 218 records in this table), but it doesn't seem to do anything since record 1 has already been changed.

Is cursor anything like recordset.movenext in ado?

"The only desert to an Irishman is an empty glass".

 

[vongrunt]

Something like (not tested):

declare @PtrVar binary(16), @InsertPos int, @DeleteLen INT

declare @PK int
declare cur cursor for select PK from [sltds_c32_e-table] order by PK
open cur

fetch next from cur into @PK
while @@fetch_status = 0
begin
	select @PtrVar = TEXTPTR(issues),
    	   @InsertPos = (PATINDEX('%Likelihood mark%', issues) -1),
	       @DeleteLen = ((PATINDEX('%mark%', issues) + 4)  - PATINDEX('%Likelihood%', issues))
	from myTable
	where PK = @PK

	UPDATETEXT [sltds_c32_e-table].issues @PtrVar @InsertPos @DeleteLen
		with Log 'LOC [Strength of Mark]'

	fetch next from cur into @PK
end
close cur
deallocate cur

As you see, cursor simply retrieves row identifier (primary key, column PK). Code within a loop is copy&paste of yours.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[toetag]

vongrunt,

You my friend are a genius Thank you. That makes perfect sense. With your example i was able to come up with:

DECLARE @PtrVar BINARY(16), @InsertPos INT, @DeleteLen INT

DECLARE @PK uniqueidentifier
DECLARE cur cursor for Select RowGUID from [sltds_c32_e-table] order by RowGUID

OPEN cur
fetch next from cur into @PK
while @@fetch_status=0
BEGIN
	select @PtrVar = TEXTPTR(issues),
       	       @InsertPos = (PATINDEX('%Likelihood of confusion strength of mark%', issues) -1),
               @DeleteLen = (
                              (PATINDEX('%mark%', issues) + 4)  - PATINDEX('%Likelihood%', issues)
       	                     )
	from [sltds_c32_e-table]
	where RowGuid = @PK

	if @InsertPos > 0
	BEGIN
		UPDATETEXT [sltds_c32_e-table].issues
                	   @PtrVar
                	   @InsertPos
                           @DeleteLen
                           with Log
                           'LOC [Strength of Mark]'
	END
	fetch next from cur into @PK
END

I haven't tested all the cases i'm going to have to replace. From the looks of it, this is going to be an answer to alot of issues i'll experience until a particular vendor fixes their "updated" program to function like the old one.

Bonus points go to you!

"The only desert to an Irishman is an empty glass".