String or binary data would be truncated - large strings

Question

We have an extremely large nvarchar(max) field that contains html. Within this html is an img tag.

Example:

<img style="float:right" src="data:image/png;base64,/9j/4AAQSkZJRgABAQEBLAEsAAD/7gAOQW....

The length of this column is 1645151, although what is being replace is a bit less than this, but not a lot.

What we are trying to do, is a replace in SQL on the column:

declare @url varchar(50) = 'myimageurl';
UPDATE table SET field =
CAST(REPLACE(CAST(field as NVARCHAR(MAX)),@source,'@url') AS NVARCHAR(MAX))

Where @source, is the above image bytes as string, which are assigned to an nvarchar(max) variable before running the replace. and dest is the url of an image, rather than the images bytes as string.

Although I still get the message string or binary data would be truncated.

Does anyone know if this is possible in SQL to replace strings as large as this.

Just thinking about the problem I'd be inclined to replace the whole field's value with the image url in place of the binary data instead of trying to do a string replace. I think that would be cleaner and easier to read. And more reliable. — Maurice Reeves
– Maurice Reeves, Commented Jul 29, 2016 at 13:25
if the length of "field" is 1645151, doesn't the outer "CAST" need to cast it to nvarchar(1645151)? The REPLACE function should already return nvarchar(max). — ADyson
– ADyson, Commented Jul 29, 2016 at 13:26
@SeanLange so it is. Lost my mind for a moment there. Which implies that "field" is already nvarchar(max), in which case why the need for any casting at all? — ADyson
– ADyson, Commented Jul 29, 2016 at 14:09
@ADyson, actually, the max value for nvarchar(n) is 4000 characters rather than 8000. — Dan Guzman
– Dan Guzman, Commented Jul 29, 2016 at 14:21

Atron Seige · Accepted Answer · 2017-06-29 14:15:37Z

4

I had the same error, but on a different function.

The fault was that my pattern has longer than my expression, which means that your search pattern will be truncated.

I hope this helps someone.

Also, make sure you put pattern and expression in the right location of your function.

answered Jun 29, 2017 at 14:15

Atron Seige

3,1314 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Noumenon Over a year ago

For example, if you type CHARINDEX(haystack, needle) instead of the right way around. I was trying to search for a 50,000-character string in a 13-character one, because I copied from Postgres strpos which has the parameters in the other order.

iamdave · Accepted Answer · 2016-07-29 16:28:29Z

Instead of doing the replace, can you rebuilt the entire field by parsing out the rest of the img tag?

Something like:

declare @Field nvarchar(max) = '<img style="float:right" src="data:image/png;base64,/9j/4AAQSkZJRgA....BAQEBLAEsAAD/7gAOQW" />'
declare @Source nvarchar(max) = 'data:image/png;base64,/9j/4AAQSkZJRgA....BAQEBLAEsAAD/7gAOQW'
declare @URL nvarchar(max) = 'www.img.img/img.png'
declare @Chars int = 20

select left(@Field,patindex('%' + left(@Source,@Chars) + '%', @Field) - 1) as HTMLStart
        ,@URL as ImgURL
        ,right(@Field,len(@Field) - patindex('%' + right(@Source,@Chars) + '%', @Field) - @Chars + 1) as HTMLEnd

If you were wanting to run this on a whole dataset at once, you would simply need to look for the src="data:image/png;base64, element and work backwards from there using a similar methodology to the above. Depends on how you are identifying which binary data to replace and what to replace it with.

Collectives™ on Stack Overflow

String or binary data would be truncated - large strings

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related