UPDATETEXT question - 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)

UPDATETEXT question

Author

Topic

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-16 : 14:31:45

Hi,

I have a situation where I have an nText field that has the hollow rectangle problem. These are empty boxes that result when data is exported from Mac ASCII files. In this case it is FileMaker to a .csv file.

I have tried to implement a script found on this site to no avail:

http://www.sqlteam.com/item.asp?ItemID=15528

Here is where I went with it: the error I get is:

invalid column name 'id'

I tried changing all instances of 'id' to 'myid' thinking it was because id is a reserved SQL term but this did not work and I got the same error:

invalid column name 'myid'

Anyone work with this problem or script?

also: you will not see the charcter for the hollow rectangle on a PC.
otxt = 'hollow rectangle'

Here is the script...

>>>>>>>>>>>>>>>>

SET xact_abort ON BEGIN TRAN DECLARE @otxt varchar(1000)
SET @otxt = '' DECLARE @ntxt varchar(1000)
SET @ntxt = '' DECLARE @txtlen int
SET @txtlen = len(@otxt) DECLARE @ptr binary(16)
DECLARE @pos int DECLARE @id int DECLARE curs CURSOR local
fast_forward FOR
SELECT id, textptr(packageCopy), charindex(@otxt,
packageCopy) - 1
FROM productsAll
WHERE packageCopy LIKE '%' + @otxt + '%' OPEN curs FETCH
next
FROM curs
INTO @id, @ptr,
@pos WHILE @@fetch_status = 0 BEGIN PRINT 'Text found in row id='
+ cast(@id AS varchar) + ' at pos=' + cast(@pos AS varchar)
UPDATETEXT productsAll.packageCopy @ptr @pos @txtlen @ntxt
FETCH next
FROM curs
INTO @id, @ptr,
@pos END CLOSE curs DEALLOCATE curs COMMIT TRAN

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-16 : 14:49:02

The only place id is used is here
SELECT id, textptr(packageCopy), charindex(@otxt, packageCopy) - 1
FROM productsAll

Do you have an id column on productsAll? It's just a row identifer for the print statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-16 : 15:06:23

Yes there is an id field in productsAll. that is why I tried changing 'id' in this script to 'myid'

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-16 : 15:15:47

OK,

I took out the print thing and it works but it says:

1 row affected by last query.

I thought this thing was to affect all the rows?

>>>>>>>>>>>>>>

SET xact_abort ON BEGIN TRAN DECLARE @otxt varchar(1000)
SET @otxt = 'hollow rect' DECLARE @ntxt varchar(1000)
SET @ntxt = '' DECLARE @txtlen int
SET @txtlen = len(@otxt) DECLARE @ptr binary(16)
DECLARE @pos int DECLARE curs CURSOR local fast_forward FOR
SELECT textptr(packageCopy), charindex(@otxt,
packageCopy) - 1
FROM productsAll
WHERE packageCopy LIKE '%' + @otxt + '%' OPEN curs FETCH
next
FROM curs
INTO @ptr,
@pos WHILE @@fetch_status = 0 BEGIN UPDATETEXT productsAll.packageCopy
@ptr @pos @txtlen @ntxt FETCH next
FROM curs
INTO @ptr,
@pos END CLOSE curs DEALLOCATE curs COMMIT TRAN

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-16 : 17:48:58

It'll affect all te rows but one row at a time (cursor).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-16 : 17:52:55

ok,

So how who I write the thing to affect all rows?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-16 : 21:40:15

You can't - updatetext will only affect one row.
This will update all rows thought due to the loop.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-17 : 16:19:58

Actually, the script will update just the first occurance of the string.... in hte first row and then stop

It does not loop through.

trying to write the script now...

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-17 : 19:39:04

Actually it works fine except it just updates the FIRST occurance of hte sting you want to replace in EACH record. If there are others you want to replace, well your up the creek without a paddle until a good loop comes along....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-17 : 19:45:26

Wow, Nigel helping someone out with a cursor. You don't see that every day.

Perhaps if you formatted your code for us better, we'd be able to help. Putting code tags around your code will keep the formatting (make sure to format it in Query Analyzer first, then copy and paste in here). Code tags look like this without the spaces:

[ c o d e]

your formatted code goes here

[ / c o d e]

Tara

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-17 : 23:54:45

OK,

The code posted at the bottom does search and replace the string but only the first occurance in each record. I need it to all occurances in all records.

I imagine a counter of sorts.


SELECT COUNT(pin) AS TheCount WHERE PackageCopy LIKE '%StringToReplace%'

This would make it so all the first ocurrance in the field PackageCopy would be replaced. It then would scan the records again and get the second occurance (now the first) and so on until


TheCount = 0

ideas, pointer, curses, .... thanks

Here is the script:


set xact_abort on
begin tran

declare @otxt varchar(1000)
set @otxt = 'StringToReplace'

declare @ntxt varchar(1000)
set @ntxt = 'NewString'

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select 
	pin,
	textptr(PackageCopy),
	charindex(@otxt, PackageCopy)-1
from 
	productsAll 
where 
	PackageCopy
like 
	'%' + @otxt +'%'

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
	print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
	
	updatetext ProductsAll.PackageCopy @ptr @pos @txtlen @ntxt

	fetch next from curs into @id, @ptr, @pos	
end

close curs
deallocate curs

commit tran

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-18 : 19:15:33

like and charindex only work on character data I believe but patindex will work for text so something like this.
Have to make sure that the new string doesn't contain the old otherwise you will need an intermediate string.

declare @otxt varchar(1000)
set @otxt = 'StringToReplace'

declare @ntxt varchar(1000)
set @ntxt = 'NewString'

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select
pin,
textptr(PackageCopy),
patindex('%' + @otxt +'%', PackageCopy)

from
productsAll
where
patindex('%' + @otxt +'%', PackageCopy) <> 0

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
while @pos <> 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
updatetext ProductsAll.PackageCopy @ptr @pos @txtlen @ntxt
select @pos = patindex('%' + @otxt +'%', PackageCopy)
end
fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-18 : 20:11:26

close and appeciate the response

This errors here:

select @pos = patindex('%' + @otxt +'%', PackageCopy)

Server: Msg 207, Level 16, State 3, Line 37
Invalid column name 'PackageCopy'.

hhmmm. 'PackageCopy' is a column

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-19 : 09:11:43

select @pos = patindex('%' + @otxt +'%', PackageCopy) from productsAll where pin = @id

pin looks like the PK for the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-19 : 13:44:57

Nigel,

The script that you showed actually just finds the very first instance then loops indefinitely through that.

I think this may be easier than it appears.

The bottom of the article says:

"If you have more than one occurrencies of your text, and you need all of them replaced, you just have to execute this script until the select statement bound to the cursor return 0 rows."

HOW DO YOU MAKE: the select statement bound to the cursor return 0 rows...

???

thanks...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-19 : 19:27:52

>> The script that you showed actually just finds the very first instance then loops indefinitely through that.

No it doesn't - try this adapted from
http://www.nigelrivett.net/InsertTextData.html

Create table productsAll (pin int, PackageCopy text)
go
create unique index ix on productsAll
(pin)
go

create proc nr_AddTextRec
@i int ,
@t varchar(8000) ,
@Action varchar(1) -- 'I insert, A append
as
declare @ptr binary(16)

if @Action = 'I'
begin
insert productsAll
(
pin ,
PackageCopy
)
select @i ,
@t
end
if @Action = 'A'
begin
select @ptr = textptr(PackageCopy)
from productsAll
where pin = @i

updatetext productsAll.PackageCopy @ptr null 0 @t
end

go
delete productsAll

exec nr_AddTextRec 1, 'asadf', 'I'
exec nr_AddTextRec 1, 'StringToReplace', 'A'
exec nr_AddTextRec 1, 'kkkkk', 'A'
exec nr_AddTextRec 1, 'StringToReplace', 'A'
exec nr_AddTextRec 1, 'kkkkk', 'A'

exec nr_AddTextRec 2, 'asadf', 'I'
exec nr_AddTextRec 2, 'StringToReplace', 'A'
exec nr_AddTextRec 2, 'kkkkk', 'A'
exec nr_AddTextRec 2, 'StringToReplace', 'A'
exec nr_AddTextRec 2, 'kkkkk', 'A'

select * from productsAll

1 asadfStringToReplacekkkkkStringToReplacekkkkk
2 asadfStringToReplacekkkkkStringToReplacekkkkk

now run the script

Text found in row id=1 at pos=6
Text found in row id=1 at pos=6
Text found in row id=1 at pos=20
Text found in row id=2 at pos=6
Text found in row id=2 at pos=6
Text found in row id=2 at pos=20

select * from productsAll

1 asadfSNewStringkkkkSNewStringkkkk
2 asadfSNewStringkkkkSNewStringkkkk

The only thing that can cause it to loop on the first occurance is if oldstring is included in newstring.

e.g. if you are updating 'old' to 'old2'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-19 : 19:31:21

needs a
select @pos = @pos - 1
before the updatetext - maybe that's it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-19 : 19:36:02

I tried it with the charindex and it doesn't work due to charindex only accepting strings not text so it won't find any values past 8000 chars.
The amended script is

declare @otxt varchar(1000)
set @otxt = 'StringToReplace'

declare @ntxt varchar(1000)
set @ntxt = 'NewString'

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select
pin,
textptr(PackageCopy),
patindex('%' + @otxt +'%', PackageCopy)

from
productsAll
where
patindex('%' + @otxt +'%', PackageCopy) <> 0

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
while @pos <> 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
select @pos = @pos - 1
updatetext ProductsAll.PackageCopy @ptr @pos @txtlen @ntxt
select @pos = patindex('%' + @otxt +'%', PackageCopy) from productsAll where pin = @id
end
fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-19 : 22:47:53

Nigel,

thanks for taking me through this one. it is definitely another level for me but I've grown in the process. Will not be able to check this out until Monday.

cheers... as they say ac\ross the pond

pelicanPaul
Starting Member

14 Posts

Posted - 2004-11-21 : 13:34:27

Nigel,

Thanks a ton... This works fine. I understand 90% of this script. Will look into patindex. Have never used that.

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-21 : 16:55:55

Patindex is similar to charindex except that it takes wildcard expressions for the text being sought and works on text data. The down side is that it doesn't have the start position.

It used to be faster than charindex but I don't know if that is still the case.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources