Dealing with a long string

4

June 25, 2014 by Kenneth Fisher

Every now and again you have to put a really long string (more than 8000 characters). Dynamic SQL is the most frequent example where I see this but I do see it elsewhere as well and it’s very easy to make a simple mistake. This is caused by the fact that a string is a varchar, at least based on all of the evidence I can find. It would probably take a real internals expert to say for sure.

-- This is the best evidence I could find of the 
-- data type of a string
SELECT SQL_VARIANT_PROPERTY('A string','BaseType');
--Returns: varchar

Note that it is varchar and not varchar(max). Varchar and varchar(max) have very different size limits. A varchar(max) has a limit of 2GB and a varchar has a limit of 8000 characters. So what is this mistake I’m talking about? Watch.

DECLARE @str varchar(max);

SET @str = REPLICATE('1',950) +
	REPLICATE('2',950) +
	REPLICATE('3',950) +
	REPLICATE('4',950) +
	REPLICATE('5',950) +
	REPLICATE('6',950) +
	REPLICATE('7',950) +
	REPLICATE('8',950) +
	REPLICATE('9',950) +
	REPLICATE('0',950); 

SELECT LEN(@str);
GO
-- Output 8000

And of course 10 * 950 characters is 9500. This is a rather contrived example but again if you are dealing with long pieces of dynamic SQL it can and does come up occasionally. So what’s the fix? Add smaller strings multiple times like this.

DECLARE @str varchar(max);

SET @str = REPLICATE('1',950) +
	REPLICATE('2',950) +
	REPLICATE('3',950) +
	REPLICATE('4',950) +
	REPLICATE('5',950); 
SET @str = @str + 
	REPLICATE('6',950) +
	REPLICATE('7',950) +
	REPLICATE('8',950) +
	REPLICATE('9',950) +
	REPLICATE('0',950); 

SELECT LEN(@str);
-- Output 9500

Personally I try to break up strings long before I run into issues, it’s safer that way. I still mess up occasionally though, and when I get a weird error that looks like my string has been truncated this is one of the first things I check for.

4 thoughts on “Dealing with a long string

  1. Rogerio Prudente says:

    Very interesting!

  2. Very interesting!, but of course they are kidding about it.

  3. Yuri Petrov says:

    Well, such error is not weird because every REPLICATE(…,950) expression has VARCHAR(8000) data type. So, whole expression has VARCHAR(8000) data type as well.
    So, it’s CORRECT that result is truncated to contain not more then 8000 symbols.
    So, to get 9500 output we need to modify expression to be VARCHAR(max).
    It’s simple because VARCHAR(max) has higher data type precedence then VARCHAR(8000).
    So, just convert one of the expression pieces to be VARCHAR(max) and your output never be truncated within 2GB:

    DECLARE @str varchar(max);

    SET @str = cast (REPLICATE(‘1’,950) as varchar (max)) +
    REPLICATE(‘2’,950) +

    REPLICATE(‘9’,950) +
    REPLICATE(‘0’,950);

    SELECT LEN(@str);

    • Fair point. Although I was actually being lazy and using the REPLICATE()s to represent actual 950 character strings. The point I was making is that a string ‘abc’ is also a varchar(8000) and when constructing a large string (a large piece of dynamic SQL or a dynamically generated delimited string) for example) you need to take that into account. It’s far too easy to forget and then you run into truncation problems.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: