June 16, 2009 at 9:12 am
Hi everybody,
I have to do a search & replace into a bunch of text type fields - in order to use UPDATETEXT I had to find the position of the text to be replaced (CHARINDEX) - I have a stored procedure that is doing this and it worked for a lot of other tasks like this. But this time the texts in the text type fields are really long (>8000 characters) and CHARINDEX(searched_string, texttype_field) returns 0, even if
WHERE texttype_field LIKE '%searched_string%') shows the right rows.
I tried this on sql2k 2000, sql 2005 & 2008.
Anybody has any workaround for this? Actually BOL doesn't write texttype fields support CHARINDEX ... And I tried PATINDEX too ...
June 16, 2009 at 9:15 am
charindex doesn't support text columns as you say so it will truncate the column before the test.
patindex should work though - check what you did.
Here are some examples
http://www.nigelrivett.net/SQLTsql/ReplaceText2.html
http://www.nigelrivett.net/SQLTsql/ReplaceText.html
Cursors never.
DTS - only when needed and never to control.
June 16, 2009 at 9:17 am
virgil i hope this might help: fiddling wiht TEXT fields is tough...here's a procedure i wrote to do find and replace in TEXT fields. I know this works on huge TEXT fields.
the code below will find and replace in every row...it builds the right sql dynamically, and executes it for as long as the oldstring is found.
if you are updating all rows, this works real well:
--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
Lowell
June 16, 2009 at 9:47 am
lol
the versions in the links I posted are a lot simpler.
Cursors never.
DTS - only when needed and never to control.
June 16, 2009 at 9:58 am
can't you use the ".Write" method of a text column in an update statement to update/replace text?
EDIT: sorry that method is for nVARCHAR(MAX)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 16, 2009 at 10:13 am
Actually I might have found a bug in PATINDEX too..
The query below returns something like :
0 es_text1
0 es_text2
0 es_text3
0 es_text4
0 es_text5
0 es_text6
select
patindex('http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html', es_text),
ES_TEXT
from MultilingualText2
where ES_TEXT LIKE '%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%'
es_text is the html source code for all kind of emails, web pages...
June 16, 2009 at 10:17 am
works for me
CREATE TABLE Table_Text
(Col1 TEXT)
INSERT INTO Table_Text
SELECT 'http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html'
select
patindex('http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html', col1), col1
from Table_Text
where col1 LIKE '%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%'
EDIT:
However if your text is something like this
'http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html And there is the URL'
Then you need to % wild cards to you padindex
something like this.
CREATE TABLE Table_Text
(Col1 TEXT)
INSERT INTO Table_Text
SELECT 'http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html And there is the URL'
select
patindex('%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%', col1) as [works],
patindex('http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html', col1) as [No works],
col1
from Table_Text
where col1 LIKE '%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%'
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 16, 2009 at 10:19 am
isn't the patindex command like this, with % signs?
select
patindex('%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%', es_text),
ES_TEXT
from MultilingualText2
CREATE TABLE Table_Text
(Col1 TEXT)
INSERT INTO Table_Text
SELECT 'blah-blah' + 'http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html' UNION ALL
SELECT 'other stuff in the textfield' + 'http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html'
select
patindex('%http://www.blabla.com/tx/chrts/nln_lstngs/cnrg_ntrm-eng.html%', col1), col1
from Table_Text
Lowell
June 16, 2009 at 10:44 am
Hello,
Oddly enough CharIndex seems to work in SQL 2005 for the VarChar(Max) Data Type. I didn’t expect that from the MS documentation.
I am not sure what the efficiency effect would be if you have to process large record sets, but I guess you could cast your Text Column to VarChar(Max). Try the following code on your test server:-
Declare @MyVar VarChar(Max)
Select @MyVar = ' Test'
Select @MyVar = Replace(@MyVar, ' ', Space(200))
Select @MyVar = Replace(@MyVar, ' ', Space(50))
Declare
@MyTable Table(MyCol Text)
Insert
@MyTable (MyCol) Values (@MyVar)
Select
CharIndex('Test', MyCol),
PatIndex('%Test%', MyCol),
SubString(MyCol, 10000, 50),
CharIndex('Test', Cast(MyCol As VarChar(Max)))
From
@MyTable
BTW - Have you considered migrating to VarChar(Max) anyway, as the Text data type is being deprecated.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 16, 2009 at 11:39 am
So I was wrong with the '%' signs ..... 🙁 Thank you a lot for all your help..
The text type is going to be deprecated in the next sql server version after 2008? - In 2008 I still can create this kind of fields...
June 16, 2009 at 12:12 pm
Hello again,
The Text Data Type is still available in SQL Server 2008. Please see:-
http://msdn.microsoft.com/en-us/library/ms187993.aspx
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply