Thanks again. I actually have (ab)used the announcement module for news, reference projects and publication. All together I think I would have to edit 60 announcements ;).
As I wrote before I tried to edit the query and acoording to your reply, I was actually searching in the right direction. I found the dbo.announcements and the Description column. When I executed the query it actually corrected the image url in the first announcement but over wrote the content of the following announcements with the content of the first.
This is the query I used (I hope pasting this code works):
CREATE Procedure Replace_Description(
@For as varchar(100),
@With as varchar(100))
AS
--* REPLACES TEXT IN THE DESCRIPTION MATCHING THE FOR VARIABLE
--* SAMPLE: Replace_Description '/Portals/0/', ''
--* MAIN DECLARATION
DECLARE
@pointer binary(16),
@index INT,
@lenFor INT,
@lenWith INT,
@diff int,
@id INT,
@count INT
--* STANDARD RUNTIME VARIABLES
SET @lenFor = LEN(@For)
SET @diff = @lenFor - LEN(@With)
--* CREATE THE TEMPORARY TABLE
CREATE TABLE #Temporary
(
[id] int, --MAPS TO YOUR SOURCE TABLE PKID
[oldtext] text, --ORIGINAL SOURCE TEXT (NOT REQUIRED)
[oldlength] int,--ORIGINAL SOURCE LENGTH
[text] text, --NEW TEXT VALUE
[length] int, --NEW LENGTH
[cLength] int --CHECKSUM LENGTH
)
--* LOOP THROUGH THE SOURCE TABLE
--* IDENTIFY ALL RECORDS WHICH MATCH YOUR PATTERN
DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT moduleid
FROM prefix_Announcements
WHERE PATINDEX('%'+@For+'%', Description)>0
OPEN irows
FETCH NEXT FROM irows INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
--* INSERT MATCHING RECORDS INTO THE TEMPORARY TABLE
INSERT INTO #Temporary(id, oldtext, oldlength, text)
SELECT
ModuleID,
Description,
datalength(Description),
Description from prefix_Announcements
where ModuleID = @id
--* GRAB THE POINTER OF THE OBJECT TO BE USED FOR UPDATETEXT
SELECT
@pointer = TEXTPTR(text)
FROM #Temporary
WHERE id=@id
--* GET THE FIRST INDEX OF OUR PATTERN
SELECT
@index = PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id
--* IF WE FOUND ANY ENTRIES, LOOP UNTIL WE REPLACE ALL OF THEM
IF @index > 0
BEGIN
select @count = 0
WHILE (
SELECT
PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id
) > 0
BEGIN
--* KEEP A RUNNING TOTAL OF IDENTIFIED ENTRIES FOR LENGTH CHECKSUM
select @count = @count + 1
SELECT
@index = PATINDEX('%'+@For+'%', text)-1
FROM #Temporary
WHERE id=@id
--* UPDATE THE TEMPORARY VALUE
UPDATETEXT #Temporary.text @pointer @index @lenFor @With
END
--* UPDATE THE TEMPORARY RECORD, SET LENGTH AND CLENGTH VALUES FOR CHECKSUM
UPDATE #Temporary set
length=datalength(text),
cLength=datalength(oldtext) - @count * @diff
WHERE id=@id
END
FETCH NEXT FROM irows INTO @id
END
CLOSE irows
DEALLOCATE irows
--* UPDATE THE DATABASE FOR ALL RECORDS IN THE TEMPORARY
--* WHERE THE CHECKSUM LENGTH MATCHED THE RESULT LENGTH
UPDATE target set
target.Description = t.[text]
FROM
prefix_Announcements target JOIN #Temporary t
ON
target.ModuleId = t.id AND
t.length = t.cLength
--* DELETE ALL RECORDS FROM TEMPORARY WHICH WERE SUCCESSFUL
DELETE from #Temporary where length=clength
--* RETURN ALL ROWS WHICH FAILED LENGTH COMPARISON
--* THIS SHOULD ALWAYS BE EMPTY
select * from #Temporary
--* DROP THE TEMP TABLE AND EXIT
DROP TABLE #Temporary |