CODEWITHSUNDEEP
sqlsql-server-2012
Louis Fellows
Louis Fellows

Reputation: 544

Converting strings longer than 8000 characters to Varbinary(MAX)

I'm trying to convert some long strings to varbinary(max) as part of an INSERT. http://msdn.microsoft.com/en-us//library/ms188362.aspx says that a varbinary(max) can hold up to 2^31-1 bytes. However, when I use:

CONVERT(varbinary(max),"ReallyLongString...")

the string gets truncated to 8000 characters.

Any ideas on how to get these strings into varbinary(max) without the truncation?

Thanks!

Upvotes: 0

Views: 6864

Answers (2)

Louis Fellows
Louis Fellows

Reputation: 544

As the answer happened in the comments, I'll add it here and close off the Question. The long string in question was made up of a number of concatenated strings.

Martin Smith gave this answer: "Make sure the first string involved in the concatenation is cast to varchar(max). varchar(n) + varchar(n) will truncate at 8,000 characters not produce a varchar(max)"

Which solved the problem. Thanks to both Martin and Joacim Isaksson for their help.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

This works on SQL Server 2012;

-- Insert
INSERT INTO myTable (value) VALUES 
    (CAST('<really long string>' AS VARBINARY(MAX)));

-- Retrieve
SELECT CAST(value AS VARCHAR(MAX)) FROM myTable;

Inserting a 10k character value will get the same value back, verified using LEN on the resulting VARCHAR(MAX).

-- Validate
SELECT LEN(CAST(value AS VARCHAR(MAX))) FROM myTable;

> 10000

An SQLfiddle to play with. Sadly SQLfiddles setup can only take 8k characters, so had to build the 10k string in a variable.
Works as above with a normal quoted string constant in a local SQL manager.

Upvotes: 3

Related Questions