October 2, 2009 at 7:43 am
I'm using Patindex on a varchar(max) column. Although it would be unusual for the column to contain more than a couple of kb of data, there are rows in the live data where the size goes over 7kb and two rows where it's over over 41kb.
An 8152 error (String or binary data would be truncated) occurs when Patindex hits these two rows. I cannot find any documentation for the maximum size supported by Patindex, but I may be looking in the wrong place. Can anyone point me to it?
October 2, 2009 at 7:52 am
hmm, I know charindex has the limit of 8k, but like you, havent seen anything else where.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 2, 2009 at 2:17 pm
Patindex works on varchar(max). (I just tested it on strings over 10k)
Are you certain that is what's causing the truncation? Patindex doesn't truncate anything, it just searches and returns an int (or with varchar(max) a bigint).
Can you provide the code?
October 5, 2009 at 4:19 am
This is enought to generate the error:
select patindex(obj,obj) from #temp3
if obj is a varchar(max) column over 42 kb long.
October 5, 2009 at 4:42 am
I don't believe patindex will work with a pattern term greater than 8k - it's fine if the column being searched is >8k.
October 5, 2009 at 4:45 am
interesting, do you have a link to back that statement up.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 4:54 am
No, I can't see any mention in BOL, but it's easy enough to demonstrate:
declare @testpattern varchar(max)
set @testpattern=''
select @testpattern=@testpattern+cast(number as varchar) from master..spt_values
where type='p'
print 'length of pattern to search for: ' + cast(len(@testpattern) as varchar)
select patindex(@testpattern,'expression')
select @testpattern=@testpattern+cast(number as varchar) from master..spt_values
where type='p'
print 'length of pattern to search for: ' + cast(len(@testpattern) as varchar)
select patindex(@testpattern,'expression')
It must pass the search term in as a 8k data type
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply