May 7, 2009 at 8:59 am
Hi,
I have a table Articles with a column called ArticleText TEXT
I have a lookup table EscCodes which stores all escape codes(" = ")
I want to find all the text from Articles table which has a value in EscCodes table and if so, replace with the corr. column in EscCodes table.
How can I do this?
May 7, 2009 at 9:08 am
Take a look at the UPDATE command. It's what you need to use.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 7, 2009 at 9:21 am
Find and Replace for TEXT/NTEXT fields is a pain.
you have to be careful, of course, because the string you replace might be part of a larger string inside your article.
I created a procedure to make it easier for me.
It replaces every instance of the oldstring, for every row in the table, unless you limit it with the @AndWhereStatement. the expected input is appended to an existing WHERE statement, so it must begin with AND , i.e. " AND SOMECOLUMN = SOMEVALUE " or "AND PK IN(2,3,5)"here's the procedure:
--usage exec pr_FindAndReplaceTextDatatype 'TABLENAME','PKCOLUMN','TEXTCOLUMN','','OLDTEXT','NEWTEXT'
--usage exec pr_FindAndReplaceTextDatatype 'TABLENAME','PKCOLUMN','TEXTCOLUMN',' AND PKID IN(3,6,9)','OLDTEXT','NEWTEXT'
--usage exec pr_FindAndReplaceTextDatatype 'GMAMEMO','ACTMEMOTBLKEY','TCOMMENT','WHERE ACTMEMOTBLKEY BETWEEN 8 AND 75','Coca Cola Classic','Just Coke'
CREATE Procedure pr_FindAndReplaceTextDatatype
--DECLARE
@TableName varchar(255),
@PKIDColumnName varchar(255),
@TextColumnName varchar(255),
@AndWhereStatement varchar(1000) = '', --AND PKID IN(2,5,7) for example
@OldString varchar(255),
@NewString varchar(255)
AS
DECLARE
@sql varchar(2000),
@LenOldString int
BEGIN
--Assign variables
SET @LenOldString=datalength(@OldString)
--string building..single quotes handled special
SET @OldString = REPLACE(@OldString,'''','''''')
SET @NewString = REPLACE(@NewString,'''','''''')
--initialize row identifier
SET @sql = ' DECLARE ' + CHAR(13)
SET @sql = @sql + ' @LenOldString int, ' + CHAR(13)
SET @sql = @sql + ' @WhichPKID int, ' + CHAR(13)
SET @sql = @sql + ' @idx int, ' + CHAR(13)
SET @sql = @sql + ' @ptr binary(16) ' + CHAR(13)
SET @sql = @sql + ' SET @LenOldString = ' + CONVERT(varchar,@LenOldString) + CHAR(13)
SET @sql = @sql + ' SELECT TOP 1 @WhichPKID = ' + @PKIDColumnName + ', ' + CHAR(13)
SET @sql = @sql + ' @idx = PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ')-1 ' + CHAR(13)
SET @sql = @sql + ' FROM ' + @TableName + ' '
SET @sql = @sql + ' WHERE PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ') > 0 ' + CHAR(13) + @AndWhereStatement + ' ' + CHAR(13)
SET @sql = @sql + ' ' + CHAR(13)
SET @sql = @sql + ' WHILE @WhichPKID > 0 ' + CHAR(13)
SET @sql = @sql + ' BEGIN ' + CHAR(13)
SET @sql = @sql + ' SELECT @ptr = TEXTPTR(' + @TextColumnName + ') ' + CHAR(13)
SET @sql = @sql + ' FROM ' + @TableName + ' ' + CHAR(13)
SET @sql = @sql + ' WHERE ' + @PKIDColumnName + ' = @WhichPKID ' + CHAR(13)
SET @sql = @sql + ' UPDATETEXT ' + @TableName + '.' + @TextColumnName + ' @ptr @idx ' + convert(varchar(30),@LenOldString) + ' ''' + @NewString + ''' ' + CHAR(13)
SET @sql = @sql + ' SET @WhichPKID = 0 ' + CHAR(13)
SET @sql = @sql + ' SELECT TOP 1 @WhichPKID = ' + @PKIDColumnName + ', @idx = PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ')-1 ' + CHAR(13)
SET @sql = @sql + ' FROM ' + @TableName + ' '
SET @sql = @sql + ' WHERE ' + @PKIDColumnName + ' > @WhichPKID ' + CHAR(13) + @AndWhereStatement + ' ' + CHAR(13)
SET @sql = @sql + ' AND PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ') > 0 ' + CHAR(13)
SET @sql = @sql + ' END ' + CHAR(13)
PRINT @sql
EXEC (@sql)
END --PROC
and here is an example to prove it works...I'm replacing part of an html string with another one..
"review" becomes "special"
Create table reviews(reviewid int identity(1,1) primary key,review_body TEXT)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
insert into reviews(review_body) select REPLICATE(' loved it liked it hated it',4000)
select * from reviews
exec pr_FindAndReplaceTextDatatype 'reviews','reviewid','review_body',' AND REVIEWID IN(3,4,5)','reviews','specialpage'
select * from reviews
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply