Replacing a string in a text field

I have a column in my database table which has lows of text and lows of rows. In the text, a url needs to be changed for every row. In each row the url can exist more than once.

Can I use the replace function to change all the urls in the text field in my database table without affecting the rest 开发者_如何学Pythonof the text in that same column?

Thanks

Use REPLACE()

UPDATE table SET text = REPLACE(text, 'from', 'to')

Make precise from: like with http://url_from.com/ to http://url_to.com/

Based on article posted at https://web.archive.org/web/20150521050102/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

I needed to write a find and replace for CHAR(146) ` in a text field. Above article for fort nText and the same solution for text worked with nText with the following changes: - VARCHAR(32) from nVARCHAR(32) - use @lenOldString = DATALENGTH(@oldString) instead of SET @lenOldString = DATALENGTH(@oldString)/2.

DECLARE @TextPointer BINARY(16), @TextIndex INT, @oldString VARCHAR(32), @newString VARCHAR(32), @lenOldString INT, @currentDataID INT;

SET @oldString = '’'; SET @newString = '''';

IF CHARINDEX(@oldString, @newString) > 0 BEGIN PRINT 'Quitting to avoid infinite loop.'; END ELSE BEGIN

--Need the for nText fields --SET @lenOldString = DATALENGTH(@oldString)/2

--Use this for text fields SET @lenOldString = DATALENGTH(@oldString)

DECLARE irows CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT DataID FROM dbo.tbData WHERE PATINDEX('%'+@oldString+'%', TextData) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @currentDataID; WHILE (@@FETCH_STATUS = 0) BEGIN

SELECT @TextPointer = TEXTPTR(TextData), @TextIndex = PATINDEX('%'+@oldString+'%', TextData) FROM dbo.tbData WHERE DataID = @currentDataID;

SELECT @TextPointer, @TextIndex

WHILE ( SELECT PATINDEX('%'+@oldString+'%', TextData) FROM dbo.tbData WHERE DataID = @currentDataID ) > 0 BEGIN

SELECT @TextIndex = PATINDEX('%'+@oldString+'%', TextData)-1 FROM dbo.tbData WHERE DataID = @currentDataID;

UPDATETEXT dbo.tbData.TextData @TextPointer @TextIndex @lenOldString @newString; END FETCH NEXT FROM irows INTO @currentDataID; END

CLOSE irows; DEALLOCATE irows;

END