Link to home
Create AccountLog in
Avatar of chetan1981
chetan1981

asked on

CHARINDEX??

Hi Gurus,

I am using charindex to search for a text in a string that is over 50K chars in length (xml field). But charindex is acting weird and only comes back with accurate data when the text occurs in the first 10000 characters or so. In case it occurs beyond that it comes back with a 0.

Any hints or alternate strategies? I am on SQL 2k5

Thanks :)
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

chetan1981,

What data type are you using for that column?

Patrick
Assuming the data type is xml, do you get a better result if you do something like:

CHARINDEX('foo', CONVERT(varchar(max), xml_column))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of jcott28
jcott28

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Charindex only ever operates on the first 8k characters, regardless of the data type. It's a flaw in the function
Tim,

So it's basically converting the inputs into [n]varchar(8000), then?

Pity :(

Patrick
Yep, only operates on the first 8000 characters.  This isn't the only function that does this...several of them do it.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>So it's basically converting the inputs into [n]varchar(8000), then?<<
This is documented in BOL starting with SQL Server 2008.  See here:
http://msdn.microsoft.com/en-us/library/ms186323.aspx
charindex(searchstring,data,position)

searchstring can be at most 8000 characters


the return value will be bigint if data is of the varchar(max), nvarchar(max) or varbinary(max) data types, otherwise int.

http://msdn.microsoft.com/en-us/library/ms186323(v=sql.90).aspx


confirm that you have defined a bigint as the return value , and that you are looking for <=8000 characters
and have converted the "data" to a varchar(max) column....
Hi, What is the exact syntax are you using for charindex ?