T-SQL 101: 49 Using REPLICATE to repeat strings in SQL Server

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here’s an example:

In this case, I’ve asked it to replicate (i.e. duplicate many times) an A character. I’ve asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE(’-’, 100).

Extra-long Strings

One challenge with this function though, is that the output data type is the same as the input data type. So even though I can have strings in T-SQL of up to 2GB in size, varchar was limited to 8000 characters. This means that if I wrote

REPLICATE('A', 10000)

I would only get 8000 characters.

But if I forced it to varchar(max), like this:

REPLICATE(CAST('A' as varchar(max)), 10000)

then I would get 10,000 A’s.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-12-23