updating a text field - 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

SQL Server Development (2000)

updating a text field

Author

Topic

miyeelee
Starting Member

1 Post

Posted - 2002-12-30 : 10:37:02

Can someone help me figure out how to modify parts of a text field. Example: In the text field, replace all occurences of 'aaa' to 'bbb'.
I tried :
update sampledb
set textcol = replace (textcol, 'aaa', 'bbb')
but recevied an error on arg1 being a text field.

Any help is greatly appreciated!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 11:23:01

Convert it to a varchar(8000) first. If fields are longer than that, the solution is trickier -- you have to break it up into sections.

- Jeff

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-12-30 : 11:29:52

Since you cannot use replace directly on a text column you need to be a bit more inventive


UPDATE sampletable
SET textcol = REPLACE(SUBSTRING(textcol,1,DATALENGTH(textcol)),'aaa','bbb') 
FROM sampletable
WHERE pk_col = whatever

HTH
Jasper Smith

shaidh
Starting Member

5 Posts

Posted - 2003-04-04 : 04:50:26

This inventive answer was just what I needed! Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 05:03:32

Don't think so - it will truncate your text column to 8000 bytes.

==========================================
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 - 2003-04-04 : 05:15:57

Think it needs to split into 8000 byte chunks to do the replace - consider if the value overelaps the 8000 byte boundary too.
Could also use patindex to find the string then do the replace and updatetext from that.

declare @ptr binary(16)
select @ptr = textptr(t) from #a where pk = @pk

declare @i int, @c varchar(7000)
select @i = 1
while @i < (select datalength(t) from #a where pk = @pk)
begin
select @c = substring(t,@i,8000) from #a where pk = @pk
select @c = replace(@c,'aaa','bbb')
updatetext #a.t @ptr @i 8000 @c
select @i = @i + 8000
end

declare @ptr binary(16)
select @ptr = textptr(t) from #a where pk = @pk

declare @i int
select @i = patindex('%aaa%',t) from #a where pk = @pk
while @i <> 0
begin
updatetext #a.t @ptr @i 3 'bbb'
select @i = patindex('%aaa%',t) from #a where pk = @pk
end

==========================================
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.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-04 : 06:14:25

quote:

Don't think so - it will truncate your text column to 8000 bytes.

How

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 06:48:25

The update replaces the text column.
Both substring and replace work on varchars so will have a max of 8000 characters and so will both lose the remaining data in the text.
substring(t,1,9000) is invalid and so is replace on a text.
Easy to test

create table #a (t text)
insert #a select replicate('a',5000) + 'b' + replicate('a',2999)
declare @ptr binary(16)
select @ptr = textptr(t) from #a
updatetext #a.t @ptr null null 'aaaaaaaabaaaaaaa'

select datalength(t) from #a
update #a set t = substring(t,1,8000) from #a
select datalength(t) from #a

drop table #a

==========================================
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.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-04 : 06:57:49

. Thanks nr.
Have some...

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 04/04/2003 07:03:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources