Bug in CHARINDEX ?

Question

Post reply

Bug in CHARINDEX ?

virgilrucsandescu

SSCrazy

Points: 2457
More actions
June 16, 2009 at 9:12 am

#155834

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 ...

Viewing 11 posts - 1 through 10 (of 10 total)

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

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--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!

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

lol

the versions in the links I posted are a lot simpler.

Cursors never.
DTS - only when needed and never to control.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

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]

SQL-4-Life

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 5

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...

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

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]

SQL-4-Life

Lowell SSC Guru Points: 323518 More actions · Answer 7

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

--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!

John H Marsh SSChampion Points: 11671 More actions · Answer 8

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

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 9

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...

John H Marsh SSChampion Points: 11671 More actions · Answer 10

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