Replace String in column

Question

Replace String in column

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Hi Alex;

Wow if you'd provided that table definition up front, we wouldn't have gone thru two pages of possible solutions and misunderstandings;

Help us help you! when you post in the future, remember we are not sitting at your desk seeing what you are...you've got to give everyone here all the data they need to duplicate your issue..just like you in the later posts, we need the CREATE TABLE, INSERT Statements for test data, and what you are trying to accomplish.

The code below is a Find-And-Replace for TEXT/NTEXT columns.

It uses a loop to go thru every row in the table, and replaces EVERY occurrence of the old value with the new value in the given TEXT column.

hope this helps:

[font="Courier New"]

DECLARE @SomePKID INT,

@ptr BINARY(16),

@idx INT

SET @SomePKID = 0

SELECT TOP 1 @SomePKID = ID, @idx = PATINDEX('%a href="%',Body)-1

FROM HTMLART

WHERE PATINDEX('%a href="%',Body) > 0

WHILE @SomePKID > 0

BEGIN

SELECT @ptr = TEXTPTR(Body)

FROM HTMLART

WHERE ID = @SomePKID

UPDATETEXT HTMLART.Body @ptr @idx 8 'a href="MyPath/' --8 is the length of a href=" that is being replaced

SET @SomePKID = 0

SELECT TOP 1 @SomePKID = ID, @idx = PATINDEX('%a href="%',Body)-1

FROM HTMLART

WHERE ID > @SomePKID

AND PATINDEX('%a href="%',Body) > 0

END [/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 2

Thanks LOWELL !!

I'm going to try right now your solution..... 😀

Thanks for your suggestion!

Alessandro

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 3

Thanks LOWELL,

but unfortunately, it doesn't work... 🙁 it doesn't change the href position in the same row.

I have more than one href in the same row. I couldn't create the script of the ROWS 'cause the Form of this Forum, transform the HTML tag. So I've included in some post ago, a txt with an example of datas.

In the file that I include right now, there's an example of the Datas..

In the first row there are more than one HREF... I've to change all. There's no problem to loop throw all the rows, I could change the source in a way to have only one row per table, but I have to change all the href in this row.

THANKS A LOT

Alessandro