Replace string in TEXT datatype column

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)

Replace string in TEXT datatype column

Author

Topic

rossale
Starting Member

3 Posts

Posted - 2009-02-05 : 10:04:21

Hi to everybody, I'm new in this community..

I would like to know if there's any way to replace a value in a TEXT datatype's column.

I have one table with some HTML Tags, and I have to decode a string inside all the columns BODY with HREF tag and changing it with new one.

I have to change the <a href=path/ApplicName into <a href=Default.asp?Lnk=path/ApplicName

Could everyone help me??

Thanks a lot

Alessandro


CREATE TABLE [HTMLART] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[BODY] TEXT COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

--- THIS IS THE ORIGINAL         ---------------------------------->

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="Test/145_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/284img.gif" width="170" /></a></td></tr><tr><td><a href="Test/146_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/284img.gif" width="170" /></a></td></tr></tbody></table>')

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="187_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/4img.gif" width="170" /></a></td></tr></tbody></table>')

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="default.pdf" title="Default" target="_blank">Link Default</a></td></tr></tbody></table>')


--- THIS IS WHAT I'M LOOKING FOR ---------------------------------->

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="default.asp?Link=Test/145_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/284img.gif" width="170" /></a></td></tr><tr><td><a href="MyPath/Test/146_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/284img.gif" width="170" /></a></td></tr></tbody></table>')

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="default.asp?Link=187_test.pdf" title="Test" target="_blank"><img alt="Immage1" border="0" height="50" src="Test/4img.gif" width="170" /></a></td></tr></tbody></table>')

INSERT INTO [HTMLART]([BODY])
VALUES('<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td><a href="default.asp?Link=default.pdf" title="Default" target="_blank">Link Default</a></td></tr></tbody></table>')

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-05 : 11:28:48

try this

declare @a table ( id int identity(1,1), x text )
insert into @a
select '<a href=path/ApplicName .......'

select replace(cast( x as nvarchar(4000)),'<a href=path/ApplicName','<a href=Default.asp?Lnk=path/ApplicName') from @a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:29:07

quote:
Originally posted by raky

try this

declare @a table ( id int identity(1,1), x text )
insert into @a
select '<a href=path/ApplicName .......'

select replace(cast( x as nvarchar(4000)),'<a href=path/ApplicName','<a href=Default.asp?Lnk=path/ApplicName') from @a

replace wont work with text . you should something like below

http://www.sqlservercentral.com/articles/Miscellaneous/handlingthetextdatatype/985/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:30:04

also this

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

rossale
Starting Member

3 Posts

Posted - 2009-02-05 : 12:35:46

Thanks Raky..
but I can't truncate the BODY column (TEXT)... and unfortunately the REPLACE function doesn't work onto TEXT fields... I've another problem too: I don't know what inside the "<a href" Tags, so I couldn't replace '<a href=path/ApplicName' directly...

Thanks for your reply!
Alessandro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:50:31

quote:
Originally posted by rossale

Thanks Raky..
but I can't truncate the BODY column (TEXT)... and unfortunately the REPLACE function doesn't work onto TEXT fields... I've another problem too: I don't know what inside the "<a href" Tags, so I couldn't replace '<a href=path/ApplicName' directly...

Thanks for your reply!
Alessandro

refer link posted. get the position of <a href using patindex and then use UPDATETXT function given in link.

rossale
Starting Member

3 Posts

Posted - 2009-02-06 : 04:26:31

quote:
Originally posted by visakh16

quote:
Originally posted by rossale

Thanks Raky..
but I can't truncate the BODY column (TEXT)... and unfortunately the REPLACE function doesn't work onto TEXT fields... I've another problem too: I don't know what inside the "<a href" Tags, so I couldn't replace '<a href=path/ApplicName' directly...

Thanks for your reply!
Alessandro

refer link posted. get the position of <a href using patindex and then use UPDATETXT function given in link.

Thanks visakh16, is exactly what I'm traying to do. The only problem is to find in the same row all the <a href referrer...

Thanks
Alessandro

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources