SQL: Is REPLICATE in SQL Server not replicating enough?

Even though I’ve worked with SQL Server for what feels like forever, I still constantly trick myself up with T-SQL commands that give me output that’s different to what I was expecting. This week, it was REPLICATE.

What should the output of the following statement be?

SELECT REPLICATE(‘Hello’, 10000);

If you said the word Hello repeated 10,000 times, or didn’t know at all, read on.

The clue comes if I execute the following statement:

SELECT LEN(REPLICATE(‘Hello’, 10000));

The output would be 8000. So we’re not getting 10,000 Hellos out of that.

The issue is that the returned data type depends upon the datatype of the first parameter. In the example above, that’s varchar. And varchar has a maximum length of 8000, so it gets silently truncated.

Same thing would have happened with nvarchar if we’d used N’Hello’ as the first parameter.

To get a longer output using varchar(max), or nvarchar(max), we need to supply a first parameter of those types, like this:

SELECT LEN(REPLICATE(CAST(‘Hello’ AS varchar(max)), 10000));

That would return 50000 as expected.

2019-03-29